Enter 2001 April/EnterCD4.iso/Update/SQL Server SP3/sql70sp3i.exe/INSTALL/ansiview.sql

Text File | 2000-09-06 | 23.5 KB | 847 lines

-- - ---------- MSSQL 7.0  ANSIVIEW.SQL 1992 System Views
set nocount on
go

-- THESE ARE "SYSTEM" OBJECTS --
exec sp_configure 'allow', 1
reconfigure with override
go
exec sp_MS_upd_sysobj_category 1
go

-- INFORMATION_SCHEMA login no longer exists...
-- INFORMATION_SCHEMA user is added by hand here:
if user_id('INFORMATION_SCHEMA') is NULL
    INSERT sysusers VALUES (3, 0, 'INFORMATION_SCHEMA', NULL, 0x00, getdate(), getdate(), 0, NULL)
go
-- NO NEED TO GRANT CREATE VIEW TO INFORMATION_SCHEMA

if object_id('INFORMATION_SCHEMA.SCHEMATA', 'V') is not NULL
    drop view INFORMATION_SCHEMA.SCHEMATA

if object_id('INFORMATION_SCHEMA.TABLES', 'V') is not NULL
    drop view INFORMATION_SCHEMA.TABLES

if object_id('INFORMATION_SCHEMA.TABLE_CONSTRAINTS', 'V') is not NULL
    drop view INFORMATION_SCHEMA.TABLE_CONSTRAINTS

if object_id('INFORMATION_SCHEMA.TABLE_PRIVILEGES', 'V') is not NULL
    drop view INFORMATION_SCHEMA.TABLE_PRIVILEGES

if object_id('INFORMATION_SCHEMA.COLUMNS', 'V') is not NULL
    drop view INFORMATION_SCHEMA.COLUMNS

if object_id('INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE', 'V') is not NULL
    drop view INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE

if object_id('INFORMATION_SCHEMA.COLUMN_PRIVILEGES', 'V') is not NULL
    drop view INFORMATION_SCHEMA.COLUMN_PRIVILEGES

if object_id('INFORMATION_SCHEMA.DOMAINS', 'V') is not NULL
    drop view INFORMATION_SCHEMA.DOMAINS

if object_id('INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS', 'V') is not NULL
    drop view INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS

if object_id('INFORMATION_SCHEMA.KEY_COLUMN_USAGE', 'V') is not NULL
    drop view INFORMATION_SCHEMA.KEY_COLUMN_USAGE

if object_id('INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS', 'V') is not NULL
    drop view INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

if object_id('INFORMATION_SCHEMA.CHECK_CONSTRAINTS', 'V') is not NULL
    drop view INFORMATION_SCHEMA.CHECK_CONSTRAINTS

if object_id('INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE', 'V') is not NULL
    drop view INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

if object_id('INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE', 'V') is not NULL
    drop view INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

if object_id('INFORMATION_SCHEMA.VIEWS', 'V') is not NULL
    drop view INFORMATION_SCHEMA.VIEWS

if object_id('INFORMATION_SCHEMA.VIEW_TABLE_USAGE', 'V') is not NULL
    drop view INFORMATION_SCHEMA.VIEW_TABLE_USAGE

if object_id('INFORMATION_SCHEMA.VIEW_COLUMN_USAGE', 'V') is not NULL
    drop view INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
go


raiserror(15339,-1,-1,'INFORMATION_SCHEMA.SCHEMATA')
go

--Identifies schmata owned by current users, databases current users has permissions in
create view INFORMATION_SCHEMA.SCHEMATA
 as
select
    db.name            as CATALOG_NAME
    ,USER_NAME()    as SCHEMA_NAME
    ,USER_NAME()    as SCHEMA_OWNER
    ,'master'        as DEFAULT_CHARACTER_SET_CATALOG
    ,'dbo'            as DEFAULT_CHARACTER_SET_SCHEMA
    ,a_cha.name        as DEFAULT_CHARACTER_SET_NAME
FROM
    master.dbo.sysdatabases         db,
    master.dbo.sysconfigures        cfg,
    master.dbo.syscharsets        a_cha, --charset/1001, not sortorder.
    master.dbo.syscharsets        b_cha --sortorder/2001, not charset.
WHERE
    cfg.comment = 'default sortorder id'
    AND a_cha.type = 1001 --- type is charset
    AND b_cha.type = 2001 --- type is sortorder
    AND a_cha.id = b_cha.csid
    AND b_cha.id = cfg.value
go

grant select on INFORMATION_SCHEMA.SCHEMATA to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.TABLES')
go
--Identifies tables accessible to the current user
create view INFORMATION_SCHEMA.TABLES
as
select  distinct
    db_name()            as TABLE_CATALOG
    ,user_name(o.uid)    as TABLE_SCHEMA
    ,o.name                as TABLE_NAME
    ,case o.xtype
        when 'U' then 'BASE TABLE'
        when 'V' then 'VIEW'
    end                    as TABLE_TYPE
from
    sysobjects o
where
    o.xtype in ('U', 'V') and
    permissions(o.id) != 0
go

grant select on INFORMATION_SCHEMA.TABLES to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.TABLE_CONSTRAINTS')
go
--Identifies table constraints owned by current users
create view INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 as
 select
    db_name()                as CONSTRAINT_CATALOG
    ,user_name(c_obj.uid)    as CONSTRAINT_SCHEMA
    ,c_obj.name                as CONSTRAINT_NAME
    ,db_name()                as TABLE_CATALOG
    ,user_name(t_obj.uid)    as TABLE_SCHEMA
    ,t_obj.name                as TABLE_NAME
    ,case c_obj.xtype
                    when 'C' then    'CHECK'
                    when 'UQ' then    'UNIQUE'
                    when 'PK' then    'PRIMARY KEY'
                    when 'F' then    'FOREIGN KEY'
                   end        as CONSTRAINT_TYPE
    ,'NO'                    as IS_DEFERRABLE
    ,'NO'                    as INITIALLY_DEFERRED
from
    sysobjects    c_obj
    ,sysobjects    t_obj
where
    c_obj.uid    = user_id()
    and t_obj.id    = c_obj.parent_obj
    and c_obj.xtype    in ('C' ,'UQ' ,'PK' ,'F')
go

grant select on INFORMATION_SCHEMA.TABLE_CONSTRAINTS to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.TABLE_PRIVILEGES')
go
--Identifies privileges granted to or by the current user
create view INFORMATION_SCHEMA.TABLE_PRIVILEGES
 as
select
    user_name(p.grantor)    as GRANTOR
    ,user_name(p.uid)        as GRANTEE
    ,db_name()                as TABLE_CATALOG
    ,user_name(o.uid)        as TABLE_SCHEMA
    ,o.name                    as TABLE_NAME
    ,case p.action
        when 26  then 'REFERENCES'
        when 193 then 'SELECT'
        when 195 then 'INSERT'
        when 196 then 'DELETE'
        when 197 then 'UPDATE'
    end                        as PRIVILEGE_TYPE
    ,case
        when p.protecttype = 205 then 'NO'
        else 'YES'
    end                        as IS_GRANTABLE
 from
    sysprotects p,
    sysobjects o
where
    (is_member(user_name(p.uid)) = 1
    or
        p.grantor = user_id())
     and (p.protecttype = 204 or     /*grant exists without same grant with grant */
    (p.protecttype = 205
        and not exists(select * from sysprotects p2
                where p2.id = p.id and
                p2.uid = p.uid and
                p2.action = p.action and
                p2.columns = p.columns and
                p2.grantor = p.grantor and
                p2.protecttype = 204)))
     and p.action in (26,193,195,196,197)
     and p.id = o.id
    and o.xtype in ('U', 'V')
     and 0 != (permissions(o.id) &
        case p.action
            when 26  then     4        /*REFERENCES basebit on all columns    */
            when 193 then     1        /*SELECT basebit on all columns    */
            when 195 then     8        /*INSERT basebit */
            when 196 then     16        /*DELETE basebit */
            when 197 then     2        /*UPDATE basebit on all columns    */
        end)
go

grant select on INFORMATION_SCHEMA.TABLE_PRIVILEGES to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.COLUMNS')
go

--Displays columns accessable to the current user
create view INFORMATION_SCHEMA.COLUMNS
 as
select
    db_name()                        as TABLE_CATALOG
    ,user_name(obj.uid)                as TABLE_SCHEMA
    ,obj.name                        as TABLE_NAME
    ,col.name                        as COLUMN_NAME
    ,col.colid                        as ORDINAL_POSITION
    ,com.text                        as COLUMN_DEFAULT
    ,case col.isnullable
        when 1 then 'YES'
        else        'No '
    end                                as IS_NULLABLE
    ,spt_dtp.LOCAL_TYPE_NAME        as DATA_TYPE
    ,col.length + spt_dtp.charbin    as CHARACTER_MAXIMUM_LENGTH
    ,col.prec + spt_dtp.charbin        as CHARACTER_OCTET_LENGTH
    ,nullif(col.xprec, 0)            as NUMERIC_PRECISION
    ,spt_dtp.RADIX                    as NUMERIC_PRECISION_RADIX
    ,col.scale                        as NUMERIC_SCALE
    ,spt_dtp.SQL_DATETIME_SUB        as DATETIME_PRECISION
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
         ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then 'master'
        else NULL
    end                                as CHARACTER_SET_CATALOG
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
        ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then 'dbo'
        else NULL
    end                                as CHARACTER_SET_SCHEMA
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
         ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then a_cha.name
        else NULL
    end                                as CHARACTER_SET_NAME
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
        ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then 'master'
        else NULL
    end                                as COLLATION_CATALOG
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
        ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then 'dbo'
        else NULL
    end                                as COLLATION_SCHEMA
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
         ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then b_cha.name
        else NULL
    end                                as COLLATION_NAME
    ,case
        when typ.xusertype < 256
            then NULL
        else DB_NAME()
    end                                as DOMAIN_CATALOG
    ,case
        when typ.xusertype < 256
            then NULL
        else USER_NAME(obj.uid)
    end                                as DOMAIN_SCHEMA
    ,case
        when typ.xusertype < 256
            then NULL
        else typ.name
    end                                as DOMAIN_NAME
FROM
    sysobjects obj,
    master.dbo.spt_datatype_info spt_dtp,
    systypes typ,
    syscolumns col
    LEFT OUTER JOIN syscomments com on col.cdefault = com.id
        AND com.colid = 1,
    master.dbo.sysconfigures    cfg,
    master.dbo.syscharsets        a_cha, --charset/1001, not sortorder.
    master.dbo.syscharsets        b_cha --sortorder/2001, not charset.
WHERE
    permissions(obj.id, col.name) != 0
    AND obj.id = col.id
    AND typ.xtype = spt_dtp.ss_dtype
    AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
    AND obj.xtype in ('U', 'V')
    AND col.xusertype = typ.xusertype
    AND spt_dtp.AUTO_INCREMENT in ( 0, null)
    AND cfg.comment = 'default sortorder id'
    AND a_cha.type = 1001 --- type is charset
    AND b_cha.type = 2001 --- type is sortorder
    AND a_cha.id = b_cha.csid
    AND b_cha.id = cfg.value
go

grant select on INFORMATION_SCHEMA.COLUMNS to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE')
go

--Identifies columns owned by current user that has a user defined datatype
create view INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
 as
select
    db_name()            as DOMAIN_CATALOG
    ,user_name(typ.uid)    as DOMAIN_SCHEMA
    ,typ.name            as DOMAIN_NAME
    ,db_name()            as TABLE_CATALOG
    ,user_name(obj.uid)    as TABLE_SCHEMA
    ,obj.name            as TABLE_NAME
    ,col.name            as COLUMN_NAME
FROM
    sysobjects obj
    ,syscolumns col
    ,systypes typ
WHERE
    obj.uid = user_id()
    AND obj.id = col.id
    AND col.xusertype = typ.xusertype
    AND typ.xusertype > 256    -- UDF Type

go

grant select on INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.COLUMN_PRIVILEGES')
go

--Identifies privileges granted to or by current user
create view INFORMATION_SCHEMA.COLUMN_PRIVILEGES
 as
select
    user_name(p.grantor)    as GRANTOR
    ,user_name(p.uid)               as GRANTEE
    ,db_name()                              as TABLE_CATALOG
    ,user_name(o.uid)               as TABLE_SCHEMA
    ,o.name                                 as TABLE_NAME
    ,c.name                                 as COLUMN_NAME
    ,case p.action
            when 193 then 'SELECT'
            when 197 then 'UPDATE'
            else 'REFERENCES'
    end                                             as PRIVILEGE_TYPE
    ,case
            when p.protecttype = 205 then 'NO'
            else 'YES'
    end                                             as IS_GRANTABLE
 from
    sysprotects p,
    sysobjects o,
    syscolumns c
where
    (is_member(user_name(p.uid)) = 1
    or
            p.grantor = user_id())
    and (p.protecttype = 204 or     /*grant exists without same grant with grant */
    (p.protecttype = 205
            and not exists(select * from sysprotects p2
                            where p2.id = p.id and
                            p2.uid = p.uid and
                            p2.action = p.action and
                            p2.columns = p.columns and
                            p2.grantor = p.grantor and
                            p2.protecttype = 204)))
    and p.action in (26,193,197)
    and p.id = o.id
    and o.xtype in ('U', 'V')
    and o.id = c.id
    and
    (((convert(tinyint,substring(p.columns,1,1))&1) = 0
            and
    (convert(int,substring(p.columns,c.colid/8+1,1))&power(2,c.colid&7)) != 0)
        or
    ((convert(tinyint,substring(p.columns,1,1))&1) != 0
            and
    (convert(int,substring(p.columns,c.colid/8+1,1))&power(2,c.colid&7)) = 0))
    and 0 != (permissions
            (o.id, c.name) &
            case p.action
                    when 26  then 4         /*REFERENCES basebit    */
                    when 193 then 1         /*SELECT basebit        */
                    when 197 then 2         /*UPDATE basebit        */
                    end)
go

grant select on INFORMATION_SCHEMA.COLUMN_PRIVILEGES to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.DOMAINS')
go

--Identifies user defined datatype accessible to current user.
create view INFORMATION_SCHEMA.DOMAINS
 as
select
    DB_NAME()                        as DOMAIN_CATALOG
    ,USER_NAME(typ.uid)                as DOMAIN_SCHEMA
    ,typ.name                        as DOMAIN_NAME
    ,spt_dtp.LOCAL_TYPE_NAME        as DATA_TYPE
    ,typ.length + spt_dtp.charbin    as CHARACTER_MAXIMUM_LENGTH
    ,typ.prec + spt_dtp.charbin        as CHARACTER_OCTET_LENGTH
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
        ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then 'master'
        else NULL
    end                                as COLLATION_CATALOG
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
        ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then 'dbo'
        else NULL
    end                                as COLLATION_SCHEMA
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
         ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then b_cha.name
        else NULL
    end                                as COLLATION_NAME
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
         ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then 'master'
        else NULL
    end                                as CHARACTER_SET_CATALOG
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
        ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then 'dbo'
        else NULL
    end                                as CHARACTER_SET_SCHEMA
    ,case
        when spt_dtp.LOCAL_TYPE_NAME in
         ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
            then a_cha.name
        else NULL
    end                                as CHARACTER_SET_NAME
    ,nullif(typ.xprec, 0)            as NUMERIC_PRECISION
    ,spt_dtp.RADIX                    as NUMERIC_PRECISION_RADIX
    ,typ.scale                        as NUMERIC_SCALE
    ,spt_dtp.SQL_DATETIME_SUB        as DATETIME_PRECISION
    ,com.text                        as DOMAIN_DEFAULT
FROM
    master.dbo.spt_datatype_info spt_dtp,
    systypes typ LEFT OUTER JOIN syscomments com
         on typ.tdefault = com.id AND com.colid = 1,
    master.dbo.sysconfigures    cfg,
    master.dbo.syscharsets        a_cha, --charset/1001, not sortorder.
    master.dbo.syscharsets        b_cha --sortorder/2001, not charset.
WHERE
     typ.xtype = spt_dtp.ss_dtype
    AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)    -- Use 7.0 entries
        AND spt_dtp.AUTO_INCREMENT in ( 0, null)    -- Remove auto increment types
    AND cfg.comment = 'default sortorder id'
    AND a_cha.type = 1001                     --- type is charset
    AND b_cha.type = 2001                    -- type is sortorder
    AND a_cha.id = b_cha.csid
    AND b_cha.id = cfg.value
    AND typ.xusertype > 256                    -- UDF Type
go

grant select on INFORMATION_SCHEMA.DOMAINS to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS')
go

--Identifies user defined datatype accessible to current user, that have constraints
create view INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
 as
select
    DB_NAME()            as CONSTRAINT_CATALOG
    ,USER_NAME(obj.uid)    as CONSTRAINT_SCHEMA
    ,obj.name            as CONSTRAINT_NAME
    ,DB_NAME()            as DOMAIN_CATALOG
    ,USER_NAME(typ.uid)    as DOMAIN_SCHEMA
    ,typ.name            as DOMAIN_NAME
    ,'NO'                as IS_DEFERRABLE
    ,'NO'                as INITIALLY_DEFERRED
FROM
    sysobjects obj,
    systypes typ
WHERE
    obj.xtype = 'R'
    and obj.id = typ.domain
    AND typ.xusertype > 256    -- UDF Type
go

grant select on INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.KEY_COLUMN_USAGE')
go

--Identifies columns owned by current which are constrained key
create view INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 as
 select
    db_name()                as CONSTRAINT_CATALOG
    ,user_name(c_obj.uid)    as CONSTRAINT_SCHEMA
    ,c_obj.name                as CONSTRAINT_NAME
    ,db_name()                as TABLE_CATALOG
    ,user_name(t_obj.uid)    as TABLE_SCHEMA
    ,t_obj.name                as TABLE_NAME
    ,col.name                as COLUMN_NAME
    ,case col.colid
        when ref.fkey1 then 1
        when ref.fkey2 then 2
        when ref.fkey3 then 3
        when ref.fkey4 then 4
        when ref.fkey5 then 5
        when ref.fkey6 then 6
        when ref.fkey7 then 7
        when ref.fkey8 then 8
        when ref.fkey9 then 9
        when ref.fkey10 then 10
        when ref.fkey11 then 11
        when ref.fkey12 then 12
        when ref.fkey13 then 13
        when ref.fkey14 then 14
        when ref.fkey15 then 15
        when ref.fkey16 then 16
    end                        as ORDINAL_POSITION
from
    sysobjects    c_obj
    ,sysobjects    t_obj
    ,syscolumns    col
    ,sysreferences  ref
where
    c_obj.uid   = user_id()
    and c_obj.xtype    in ('F ')
    and t_obj.id    = c_obj.parent_obj
    and t_obj.id    = col.id
    and col.colid   in
    (ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6,
    ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12,
    ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16)
    and c_obj.id    = ref.constid
union
 select
    db_name()                as CONSTRAINT_CATALOG
    ,user_name(c_obj.uid)    as CONSTRAINT_SCHEMA
    ,i.name                    as CONSTRAINT_NAME
    ,db_name()                as TABLE_CATALOG
    ,user_name(t_obj.uid)    as TABLE_SCHEMA
    ,t_obj.name                as TABLE_NAME
    ,col.name                as COLUMN_NAME
    ,v.number                as ORDINAL_POSITION
from
    sysobjects        c_obj
    ,sysobjects        t_obj
    ,syscolumns        col
    ,master.dbo.spt_values     v
    ,sysindexes        i
where
    c_obj.uid    = user_id()
    and c_obj.xtype    in ('UQ' ,'PK')
    and t_obj.id    = c_obj.parent_obj
    and t_obj.xtype  = 'U'
    and t_obj.id    = col.id
    and col.name    = index_col(t_obj.name,i.indid,v.number)
    and t_obj.id    = i.id
    and c_obj.name  = i.name
    and v.number     > 0
     and v.number     <= i.keycnt
     and v.type     = 'P'
go

grant select on INFORMATION_SCHEMA.KEY_COLUMN_USAGE to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS')
go

--Identifies foreign constraints owned by current user.
create view INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 as
 select
    db_name()                as CONSTRAINT_CATALOG
    ,user_name(fc_obj.uid)    as CONSTRAINT_SCHEMA
    ,fc_obj.name            as CONSTRAINT_NAME
    ,db_name()                as UNIQUE_CONSTRAINT_CATALOG
    ,user_name(pc_obj.uid)    as UNIQUE_CONSTRAINT_SCHEMA
    ,i.name                    as UNIQUE_CONSTRAINT_NAME
    ,'PARTIAL'                as MATCH_OPTION
    ,'NO ACTION'            as UPDATE_RULE
    ,'NO ACTION'            as DELETE_RULE
from
    sysobjects    fc_obj
    ,sysreferences    r
    ,sysindexes    i
    ,sysobjects    pc_obj
where
    fc_obj.uid        = user_id()
    and fc_obj.xtype    = 'F'
    and r.constid        = fc_obj.id
    and r.rkeyid        = i.id
    and r.rkeyindid     = i.indid
    and r.rkeyid        = pc_obj.id
go

grant select on INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.CHECK_CONSTRAINTS')
go

--Identifies check constraints owned by current user
create view INFORMATION_SCHEMA.CHECK_CONSTRAINTS
as
select
    db_name()                as CONSTRAINT_CATALOG
    ,user_name(c_obj.uid)    as CONSTRAINT_SCHEMA
    ,c_obj.name                as CONSTRAINT_NAME
    ,com.text                as CHECK_CLAUSE
from
    sysobjects    c_obj
    ,syscomments    com
where
    c_obj.uid    = user_id()
    and c_obj.id    = com.id
    and c_obj.xtype    = 'C'
go

grant select on INFORMATION_SCHEMA.CHECK_CONSTRAINTS to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE')
go

--Identifies tables owned by the current user the have constraints
create view INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
 as
 select
    db_name()                as TABLE_CATALOG
    ,user_name(t_obj.uid)    as TABLE_SCHEMA
    ,t_obj.name                as TABLE_NAME
    ,db_name()                as CONSTRAINT_CATALOG
    ,user_name(c_obj.uid)    as CONSTRAINT_SCHEMA
    ,c_obj.name                as CONSTRAINT_NAME
from
    sysobjects    c_obj
    ,sysobjects    t_obj
where
    t_obj.uid    = user_id()
    and t_obj.id    = c_obj.parent_obj
    and c_obj.xtype    in ('C' ,'UQ' ,'PK' ,'F')
go

grant select on INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE')
go

--Identifies tables and columns owned by the current user the have constraints
create view INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
(
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,CONSTRAINT_CATALOG
,CONSTRAINT_SCHEMA
,CONSTRAINT_NAME
)
 as
 select
    KCU.TABLE_CATALOG    /*TABLE_CATALOG*/
    ,KCU.TABLE_SCHEMA     /*TABLE_SCHEMA*/
    ,KCU.TABLE_NAME     /*TABLE_NAME*/
    ,KCU.COLUMN_NAME     /*COLUMN_NAME*/
    ,KCU.CONSTRAINT_CATALOG/*CONSTRAINT_CATALOG*/
    ,KCU.CONSTRAINT_SCHEMA /*CONSTRAINT_SCHEMA*/
    ,KCU.CONSTRAINT_NAME     /*CONSTRAINT_NAME*/
from
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
UNION
 select
    db_name()            /*TABLE_CATALOG*/
    ,user_name(t_obj.uid)/*TABLE_SCHEMA*/
    ,t_obj.name            /*TABLE_NAME*/
    ,cols.name            /*COLUMN_NAME*/
    ,db_name()            /*CONSTRAINT_CATALOG*/
    ,user_name(c_obj.uid)/*CONSTRAINT_SCHEMA*/
    ,c_obj.name            /*CONSTRAINT_NAME*/
from
    sysobjects    t_obj
    ,sysobjects    c_obj
    ,syscolumns cols
where
    t_obj.uid    = user_id()
    and t_obj.id    = c_obj.parent_obj
    and c_obj.xtype    = 'C'
    and c_obj.info    = cols.colid
    and cols.id        = c_obj.parent_obj
UNION
select
    db_name()        /*TABLE_CATALOG*/
    ,user_name(t_obj.uid)    /*TABLE_SCHEMA*/
    ,t_obj.name        /*TABLE_NAME*/
    ,col.name        /*COLUMN_NAME*/
    ,db_name()        /*CONSTRAINT_CATALOG*/
    ,user_name(r_obj.uid)    /*CONSTRAINT_SCHEMA*/
    ,r_obj.name        /*CONSTRAINT_NAME*/
FROM
    sysobjects t_obj
    ,syscolumns col
    ,systypes typ
    ,sysobjects  r_obj
WHERE
    t_obj.uid = user_id()
    AND t_obj.id = col.id
    AND col.xusertype = typ.xusertype
    AND typ.xusertype > 256    -- UDF Type
    AND typ.domain = r_obj.id
    AND r_obj.xtype = 'R'
go

grant select on INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.VIEWS')
go

-- Displays views accessable to current user
create view INFORMATION_SCHEMA.VIEWS
 as
 select
    db_name()                as TABLE_CATALOG
    ,user_name(obj.uid)        as TABLE_SCHEMA
    ,obj.name                as TABLE_NAME
    ,case
        when exists (select *
            from syscomments com3
            where com3.id = obj.id
            and com3.colid > 1)    then NULL
        else com.text
    end                        as VIEW_DEFINITION
    ,case
        when exists (select *
            from syscomments com2
            where com2.id = obj.id
            and CHARINDEX('WITH CHECK OPTION',
            upper(com2.text)) > 0)  then 'CASCADE'
        else 'NONE'
    end                        as CHECK_OPTION
    ,'NO'                    as IS_UPDATABLE
from
    sysobjects obj
    ,syscomments com
where
    permissions(obj.id) != 0
    and obj.xtype    = 'V'
    and obj.id      = com.id
    and com.colid    = 1
go

grant select on INFORMATION_SCHEMA.VIEWS to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.VIEW_TABLE_USAGE')
go

-- Identifies views owned by current user and the tables used in their definition
create view INFORMATION_SCHEMA.VIEW_TABLE_USAGE
 as
select distinct
    db_name()                as VIEW_CATALOG
    ,user_name(v_obj.uid)    as VIEW_SCHEMA
    ,v_obj.name                as VIEW_NAME
    ,db_name()                as TABLE_CATALOG
    ,user_name(t_obj.uid)    as TABLE_SCHEMA
    ,t_obj.name                as TABLE_NAME
from
     sysobjects    t_obj
    ,sysobjects    v_obj
    ,sysdepends    dep
where
    v_obj.uid    = user_id()
    and v_obj.xtype    = 'V'
    and dep.id     = v_obj.id
    and dep.depid    = t_obj.id

go

grant select on INFORMATION_SCHEMA.VIEW_TABLE_USAGE to public
go

raiserror(15339,-1,-1,'INFORMATION_SCHEMA.VIEW_COLUMN_USAGE')
go

-- Identifies views owned by current user and the columns used in their definition
create view INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
 as
select
    db_name()                as VIEW_CATALOG
    ,user_name(v_obj.uid)    as VIEW_SCHEMA
    ,v_obj.name                as VIEW_NAME
    ,db_name()                as TABLE_CATALOG
    ,user_name(t_obj.uid)    as TABLE_SCHEMA
    ,t_obj.name                as TABLE_NAME
    ,col.name                as COLUMN_NAME
from
     sysobjects    t_obj
    ,sysobjects    v_obj
    ,sysdepends    dep
    ,syscolumns col

where
    v_obj.uid    = user_id()
    and v_obj.xtype    = 'V'
    and dep.id    = v_obj.id
    and dep.depid    = t_obj.id
    and t_obj.id    = col.id
    and dep.depnumber    = col.colid
go

grant select on INFORMATION_SCHEMA.VIEW_COLUMN_USAGE to public
go



-- END OF "SYSTEM" OBJECT CREATION --
exec sp_MS_upd_sysobj_category 2
go
exec sp_configure 'allow', 0
reconfigure with override
go