SQL所有表、存储过程、触发器

所有表:select * from sysobjects where xtype='U'  或 select * from sys.tables

所有存储过程:select * from sysobjects where xtype='P'  或  select * from sys.procedures 

所有触发器:select  * from  sysobjects where xtype='TR'   或 select * from  sys.triggers

触发器的状态及语句(同一触发器写的很少的话,系统会发为多行记录)

select  object_name(a.parent_obj) as [表名]

,a.name as [触发器名称]
,(case when b.is_disabled=0 then '启用' else '禁用' end) as [状态]
,b.create_date as [创建日期]
,b.modify_date as [修改日期]
,c.text as [触发器语句]
from sysobjects a
inner join sys.triggers b
on b.object_id=a.id
inner join syscomments c
on c.id=a.id
where a.xtype='TR'
order  by [表名]

所有函数、存储过程、触发器中的内容:select * from  syscomments

-----------------------------------

所有索引:

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)
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)
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

-----------------------------------------------------------------

 

posted @ 2022-01-21 16:15  木头侠  阅读(98)  评论(0编辑  收藏  举报