Sqlserver数据字典

以下内容出自cookbook

/*列出模式中的表*/ select table_name ? from information_schema.tables ?where table_schema = 'SMEAGOL' /*列出表的列*/ select column_name,data_type,ordinal_position ? from information_schema.columns ?where table_schema = 'SMEAGOL' ? ?and table_name ? = 'EMP' /*列出表的索引列*/ select a.name table_name,? ? ? ?b.name index_name,? ? ? ?d.name column_name,? ? ? ?c.index_column_id ? from sys.tables a,? ? ? ?sys.indexes b,? ? ? ?sys.index_columns c,? ? ? ?sys.columns d where a.object_id = b.object_id ? and b.object_id = c.object_id ? and b.index_id ?= c.index_id ? and c.object_id = d.object_id ? and c.column_id = d.column_id ? and a.name ? ? ?= 'EMP' /*列出表约束*/ select a.table_name,? ? ? ?a.constraint_name,? ? ? ?b.column_name,? ? ? ?a.constraint_type ? from information_schema.table_constraints a,? ? ? ?information_schema.key_column_usage b where a.table_name ? ? ?= 'EMP' ? and a.table_schema ? ?= 'SMEAGOL' ? and a.table_name ? ? ?= b.table_name ? and a.table_schema ? ?= b.table_schema ? and a.constraint_name = b.constraint_name /*列出没有相应索引的外键*/ select fkeys.table_name,? ? ? ?fkeys.constraint_name,? ? ? ?fkeys.column_name,? ? ? ?ind_cols.index_name ? from ( select a.object_id,? ? ? ?d.column_id,? ? ? ?a.name table_name,? ? ? ?b.name constraint_name,? ? ? ?d.name column_name ? from sys.tables a ? ? ? ?join ? ? ? ?sys.foreign_keys b ? ? on ( a.name ? ? ? ? ?= 'EMP' ? ? ? ? ?and a.object_id = b.parent_object_id ? ? ? ?) ? ? ? ?join ? ? ? ?sys.foreign_key_columns c ? ?on ( ?b.object_id = c.constraint_object_id ) ? ? ? join ? ? ? sys.columns d ? ?on ( ? ?c.constraint_column_id = d.column_id ? ? ? ?and a.object_id ? ? ? ? ? ?= d.object_id ? ? ? ) ? ? ? ) fkeys ? ? ? left join ? ? ? ( elect a.name index_name,? ? ? b.object_id,? ? ? b.column_id ?from sys.indexes a,? ? ? sys.index_columns b where a.index_id = b.index_id ? ? ? ) ind_cols ? ?on ( ? ? fkeys.object_id = ind_cols.object_id ? ? ? ? and fkeys.column_id = ind_cols.column_id ) where ind_cols.index_name is null

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!