SQL code
-- 查询用户表对象信息 select Tab.Name as [ 表名 ] ,Tab.create_date as [ 创建时间 ] ,Tab.modify_date as [ 最后修改时间 ] , Col.Name as [ 列名 ] ,Type.name as [ 数据类型 ] ,Col.max_length as [ 字段长度 ] , CASE WHEN pk.is_primary_key = 1 THEN ' Y ' ELSE ' N ' end as [ 是否主键 ] , CASE WHEN Col.is_identity = 1 THEN ' Y ' else ' N ' end as [ 是否自增 ] , identity_columns.seed_value as [ 自增种子 ] ,identity_columns.increment_value as [ 自增步长 ] , case when Col.is_nullable = 1 then ' Y ' else ' N ' END AS [ 是否允许为NULL ] , Def. text as [ 默认值 ] , case when Col.is_computed = 1 then ' Y ' else ' N ' END as [ 是否计算列 ] , computed_columns.definition as [ 计算公式 ] ,Col_Desc.Value as [ 列备注 ] from sys.objects Tab inner join sys.columns Col on Tab. object_id = Col. object_id inner join sys.types Type on Col.system_type_id = Type.system_type_id left join sys.identity_columns identity_columns on Tab. object_id = identity_columns. object_id and Col.column_id = identity_columns.column_id left join syscomments Def on Col.default_object_id = Def.ID left join ( select index_columns. object_id ,index_columns.column_id,indexes.is_primary_key from sys.indexes indexes inner join sys.index_columns index_columns on indexes. object_id = index_columns. object_id and indexes.index_id = index_columns.index_id where indexes.is_primary_key = 1 /* 主键 */ ) PK on Tab. object_id = PK. object_id AND Col.column_id = PK.column_id left join sys.computed_columns computed_columns on Tab. object_id = computed_columns. object_id and Col.column_id = computed_columns.column_id left join sys.extended_properties Col_Desc on Col_Desc.major_id = Tab. object_id and Col_Desc.minor_id = Col.Column_id and Col_Desc.class = 1 where Tab.type = ' U ' and Tab.Name not like ' sys% ' order by Tab.create_date -- 查询所有视图 select views.Name as [ 视图名 ] ,Col.Name as [ 列名 ] ,Type.name as [ 数据类型 ] ,Col.max_length as [ 字段长度 ] -- ,Col_Desc.Value as Col_Description from sys.views views inner join sys.columns Col on views. object_id = Col. object_id inner join sys.types Type on Col.system_type_id = Type.system_type_id -- left join sys.extended_properties Col_Desc -- on Col_Desc.major_id=views.object_id and Col_Desc.minor_id=Col.Column_id and Col_Desc.class=1 order by Create_Date -- 查询外键约束 select FK_Name as [ 外键名 ] ,Parent_Tab_Name as [ 外键表 ] , [ 外键列 ] = stuff (( select ' ， ' + [ Parent_Col_Name ] from ( select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name, Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name from sys.foreign_keys FK inner join sys.foreign_key_columns Col on FK. Object_ID = Col.constraint_object_id inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab. Object_ID and Parent_Tab.TYPE = ' U ' inner join sys.columns Parent_Col on Parent_Tab. Object_ID = Parent_Col. object_id and Col.parent_column_id = Parent_Col.column_id inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab. Object_ID and Referenced_Tab.TYPE = ' U ' inner join sys.columns Referenced_Col on Referenced_Tab. Object_ID = Referenced_Col. object_id and Col.referenced_column_id = Referenced_Col.column_id )t where FK_Name = tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name for xml path( '' )), 1 , 1 , '' ), Referenced_Tab_Name as [ 主键表 ] , [ 主键列 ] = stuff (( select ' ， ' + [ Referenced_Col_Name ] from ( select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name, Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name from sys.foreign_keys FK inner join sys.foreign_key_columns Col on FK. Object_ID = Col.constraint_object_id inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab. Object_ID and Parent_Tab.TYPE = ' U ' inner join sys.columns Parent_Col on Parent_Tab. Object_ID = Parent_Col. object_id and Col.parent_column_id = Parent_Col.column_id inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab. Object_ID and Referenced_Tab.TYPE = ' U ' inner join sys.columns Referenced_Col on Referenced_Tab. Object_ID = Referenced_Col. object_id and Col.referenced_column_id = Referenced_Col.column_id )t where FK_Name = tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name for xml path( '' )), 1 , 1 , '' ) -- as [外键列] from ( select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name, Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name from sys.foreign_keys FK inner join sys.foreign_key_columns Col on FK. Object_ID = Col.constraint_object_id inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab. Object_ID and Parent_Tab.TYPE = ' U ' inner join sys.columns Parent_Col on Parent_Tab. Object_ID = Parent_Col. object_id and Col.parent_column_id = Parent_Col.column_id inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab. Object_ID and Referenced_Tab.TYPE = ' U ' inner join sys.columns Referenced_Col on Referenced_Tab. Object_ID = Referenced_Col. object_id and Col.referenced_column_id = Referenced_Col.column_id )tb group by FK_Name,Parent_Tab_Name,Referenced_Tab_Name -- 查询所有存储过程 select Pr_Name as [ 存储过程 ] , [ 参数 ] = stuff (( select ' ， ' + [ Parameter ] from ( select Pr.Name as Pr_Name,parameter.name + ' ' + Type.Name + ' ( ' + convert ( varchar ( 32 ),parameter.max_length) + ' ) ' as Parameter from sys.procedures Pr left join sys.parameters parameter on Pr. object_id = parameter. object_id inner join sys.types Type on parameter.system_type_id = Type.system_type_id where type = ' P ' ) t where Pr_Name = tb.Pr_Name for xml path( '' )), 1 , 1 , '' ) from ( select Pr.Name as Pr_Name,parameter.name + ' ' + Type.Name + ' ( ' + convert ( varchar ( 32 ),parameter.max_length) + ' ) ' as Parameter from sys.procedures Pr left join sys.parameters parameter on Pr. object_id = parameter. object_id inner join sys.types Type on parameter.system_type_id = Type.system_type_id where type = ' P ' )tb where Pr_Name not like ' sp_% ' -- and Pr_Name not like 'dt%' group by Pr_Name order by Pr_Name -- 查询所有触发器 select triggers.name as [ 触发器 ] ,tables.name as [ 表名 ] ,triggers.is_disabled as [ 是否禁用 ] , triggers.is_instead_of_trigger AS [ 触发器类型 ] , case when triggers.is_instead_of_trigger = 1 then ' INSTEAD OF ' when triggers.is_instead_of_trigger = 0 then ' AFTER ' else null end as [ 触发器类型描述 ] from sys.triggers triggers inner join sys.tables tables on triggers.parent_id = tables. object_id where triggers.type = ' TR ' order by triggers.create_date -- 查询所有索引 select indexs.Tab_Name as [ 表名 ] ,indexs.Index_Name as [ 索引名 ] ,indexs. [ Co_Names ] as [ 索引列 ] , Ind_Attribute.is_primary_key as [ 是否主键 ] ,Ind_Attribute.is_unique AS [ 是否唯一键 ] , Ind_Attribute.is_disabled AS [ 是否禁用 ] from ( select Tab_Name,Index_Name, [ Co_Names ] = stuff (( select ' ， ' + [ Co_Name ] from ( select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind inner join sys.tables tab on ind. Object_id = tab. object_id and ind.type in ( 1 , 2 ) /* 索引的类型：0=堆/1=聚集/2=非聚集/3=XML */ inner join sys.index_columns index_columns on tab. object_id = index_columns. object_id and ind.index_id = index_columns.index_id inner join sys.columns Col on tab. object_id = Col. object_id and index_columns.column_id = Col.column_id ) t where Tab_Name = tb.Tab_Name and Index_Name = tb.Index_Name for xml path( '' )), 1 , 1 , '' ) from ( select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind inner join sys.tables tab on ind. Object_id = tab. object_id and ind.type in ( 1 , 2 ) /* 索引的类型：0=堆/1=聚集/2=非聚集/3=XML */ inner join sys.index_columns index_columns on tab. object_id = index_columns. object_id and ind.index_id = index_columns.index_id inner join sys.columns Col on tab. object_id = Col. object_id and index_columns.column_id = Col.column_id )tb where Tab_Name not like ' sys% ' group by Tab_Name,Index_Name ) indexs inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name order by indexs.Tab_Name
740
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
