查sqlserver版本信息, 实例名, 服务器名以及SP,trigger等object的定义,列出表名

select @@serviceNAME  
select @@version  
select @@SERVERNAME

查询某段SP, trigger的定义

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 [触发器类型描述],
c.text
from sys.triggers triggers
inner join sys.tables tables on triggers.parent_id = tables.object_id
INNER JOIN syscomments c  ON c.id=triggers.object_id  
where triggers.type ='TR' and c.text like '%some code%'
order by triggers.create_date

查询某个schema下带某列的表名:

select t.name,t.object_id,s.name,sc.name from sys.tables t
join syscolumns sc on sc.id= Object_Id(t.name) and sc.name like 'source_id'
join sys.schemas as s on t.schema_id = s.schema_id and s.[name] = 'dbo'