SQL SERVER常用的数据字典

在SQL SERVER2000中查找所有的外键:

 

select * from information_schema.referential_constraints

 

查找所有带ON DELETE CASCADE的外建:

 

select * from information_schema.referential_constraints where delete_rule='CASCADE'

 

 

 

查找所有的外键字段：

 

SELECT A.TABLE_NAME,A.CONSTRAINT_NAME,A.COLUMN_NAME FROM information_schema.key_column_usage a,

 

information_schema.table_constraints b

 

WHERE A.TABLE_NAME=B.TABLE_NAME

 

AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME

 

AND b.constraint_type='FOREIGN KEY'    

 

ORDER BY A.TABLE_NAME,A.CONSTRAINT_NAME,a.ordinal_position

 

或：

 

SELECT A.TABLE_NAME,A.CONSTRAINT_NAME,A.COLUMN_NAME FROM information_schema.constraint_column_usage a,

 

information_schema.table_constraints b

 

WHERE A.TABLE_NAME=B.TABLE_NAME

 

AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME

 

AND b.constraint_type='FOREIGN KEY'

 

ORDER BY A.TABLE_NAME,A.CONSTRAINT_NAME

 

 

 

查找所有的外键字段，其参考的主表，主表字段：

 

SQL SERVER 2000的写法：

 

select  fk_tab.table_name as fk_table,a.constraint_name as fk,

 

fk_col.column_name as fk_column,fk_col.ordinal_position as fk_col_ord,

 

pk_tab.table_name as pk_table,

 

a.unique_constraint_name as pk,

 

pk_col.column_name as pk_column,

 

pk_col.ordinal_position as pk_col_ord

 

 from information_schema.referential_constraints a,

 

information_schema.constraint_table_usage fk_tab,

 

information_schema.constraint_table_usage pk_tab,

 

information_schema.key_column_usage fk_col,

 

information_schema.key_column_usage pk_col

 

where a.constraint_name=fk_tab.constraint_name

 

and  fk_tab.table_name=fk_col.table_name

 

and fk_tab.constraint_name=fk_col.constraint_name

 

and a.unique_constraint_name=pk_tab.constraint_name

 

and pk_tab.table_name=pk_col.table_name

 

and pk_tab.constraint_name=pk_col.constraint_name 

 

and fk_col.ordinal_position=pk_col.ordinal_position

 

and a.constraint_catalog='anf_hk'

 

and a.constraint_schema='xpc71pilot'

 

order by fk_table,fk,fk_col_ord

 

 

 

SQL SERVER 2005：

 

select a.parent_object_id,c.name as table_name ,

 

a.constraint_object_id,b.name as constraint_objname,

 

a.parent_column_id,d.name as parent_colname,a.referenced_object_id,

 

e.name as referenced_tablename,a.referenced_column_id,

 

f.name as referenced_colname

 

from sys.foreign_key_columns a,sys.objects b,sys.objects c,sys.columns d,

 

sys.objects e,sys.columns f

 

where a.constraint_object_id=b.object_id

 

and a.parent_object_id=c.object_id

 

and c.object_id=d.object_id and d.column_id=a.parent_column_id

 

and a.referenced_object_id=e.object_id

 

and  f.object_id=e.object_id

 

and  f.column_id=a.referenced_column_id

 

order by table_name,constraint_objname

 

或：

 

select c.name as table_name ,

 

b.name as constraint_objname,

 

d.name as parent_colname,

 

e.name as referenced_tablename,

 

f.name as referenced_colname

 

from sys.foreign_key_columns a,sys.objects b,sys.objects c,sys.columns d,

 

sys.objects e,sys.columns f

 

where a.constraint_object_id=b.object_id

 

and a.parent_object_id=c.object_id

 

and c.object_id=d.object_id and d.column_id=a.parent_column_id

 

and a.referenced_object_id=e.object_id

 

and  f.object_id=e.object_id

 

and  f.column_id=a.referenced_column_id

 

order by table_name,constraint_objname

 

 

 

 

 

查找所有的索引：

 

SQL SERVER 2000：

 

select b.name as table_name,

 

a.name as index_name,

 

e.name as index_col_name,

 

c.keyno  from

 

sysindexes a,sysobjects b,sysindexkeys c,syscolumns e,sysusers f

 

where a.id=b.id

 

and a.indid=c.indid

 

and  c.id=b.id

 

and  c.id=e.id

 

and  c.colid=e.colid

 

and  b.uid=f.uid

 

and f.name='xpc71pilot'

 

and a.name not like '/_%' escape '/'

 

order by table_name,index_name,c.keyno

 

 

 

 

 

SQL SERVER 2005：

 

select a.object_id,b.name as table_name,a.index_id,a.name as index_name ,c.key_ordinal,

 

c.index_column_id,

 

d.name as index_column_name,

 

a.type_desc,a.is_unique,a.is_primary_key,

 

a.is_unique_constraint,

 

c.is_descending_key,

 

a.ignore_dup_key,a.is_disabled,a.allow_row_locks,

 

a.allow_page_locks

 

from sys.indexes a,sys.objects b, sys.index_columns c,sys.columns d,

 

sys.schemas e

 

where a.object_id=b.object_id

 

and a.index_id=c.index_id

 

and  c.object_id=d.object_id

 

and  b.object_id=d.object_id

 

and  c.column_id=d.column_id

 

and e.schema_id=b.schema_id

 

and  e.name='xpc90_license_1'

 

order by table_name,a.index_id,c.key_ordinal

 

 

 

或：

 

select b.name as table_name,a.name as index_name ,c.key_ordinal,

 

d.name as index_column_name,

 

a.type_desc,a.is_unique,a.is_primary_key,

 

a.is_unique_constraint,

 

c.is_descending_key,

 

a.ignore_dup_key,a.is_disabled,a.allow_row_locks,

 

a.allow_page_locks

 

from sys.indexes a,sys.objects b, sys.index_columns c,sys.columns d,

 

sys.schemas e

 

where a.object_id=b.object_id

 

and a.index_id=c.index_id

 

and  c.object_id=d.object_id

 

and  b.object_id=d.object_id

 

and  c.column_id=d.column_id

 

and e.schema_id=b.schema_id

 

and  e.name='xpc90_license_1'

 

order by table_name,a.index_id,c.key_ordinal

 

 

 

SQL SERVER 2000中获取所有的表名，字段名，字段数据类型等：

 

select a.table_name,b.column_name,b.data_type,b.ordinal_position,

 

b.is_nullable,b.column_default,b.numeric_precision,b.numeric_scale,

 

b.character_maximum_length

 

from information_schema.tables a,

 

information_schema.columns b

 

where a.table_name=b.table_name

 

and a.table_catalog=b.table_catalog

 

and a.table_schema=b.table_schema

 

and a.table_catalog='anf_hk_new'

 

and a.table_schema='xpc71pilot'

 

order by a.table_name,b.ordinal_position

 

 

 

 

 

常用的数据字典表：

 

select * from sys.index_columns  --索引字段

 

select * from sys.indexes    --索引

 

select * from sys.objects   --对象

 

select * from sys.foreign_key_columns --外键字段

 

select * from sys.foreign_keys --外键

 

select * from sys.tables   --表

 

select * from sys.columns   --字段

 

select * from sys.schemas   --模式

 

SELECT * FROM information_schema.tables  --表（sql2000）

 

select * from information_schema.key_column_usage --键字段

 

select * from information_schema.table_constraints  --表的约束

 

select * from information_schema.constraint_column_usage—约束字段

 

select * from sysusers ORDER BY NAME

 

--

 

--获取表主外键约束

 

exec sp_helpconstraint  'pm_user' ;

 

--sp_fkeys和sp_pkeys分别显示表的全部外键和主键

 

exec sp_fkeys 'pm_user'

 

exec sp_pkeys 'pm_user'