全国服务热线:4008-888-888

行业新闻

SqlServer常见目标查寻

-- 查寻客户表目标信息内容 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

SqlServer常见目标查寻

--查寻客户表目标信息内容

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 puted = 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 ments 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 puted_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. >
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. >
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)/*数据库索引的种类:=堆/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)/*数据库索引的种类:=堆/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 共享到:
文中"SqlServer常见目标查寻"由远航网站站长搜集梳理而成,仅作大伙儿学习培训与参照应用。大量网站建设实例教程尽在远航网站站长站。


在线客服

关闭

客户服务热线
4008-888-888


点击这里给我发消息 在线客服

点击这里给我发消息 在线客服