查询数据库表、字段、备注
查询表、字段、备注-- 名名称和字段名
SELECT (CASE WHEN a.colorder=1 THEN d.name ELSE d.name END) 表名, a.colorder 字段序号, a.name 字段名, (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')=1 THEN '√' ELSE '' END) 标识, (CASE WHEN (SELECT COUNT(*)
FROM sysobjects
WHERE(name IN(SELECT name
FROM sysindexes
WHERE(id=a.id)AND(indid IN(SELECT indid
FROM sysindexkeys
WHERE(id=a.id)AND(colid IN(SELECT colid FROM syscolumns WHERE(id=a.id)AND(name=a.name)))))))AND(xtype='PK'))>0 THEN '√' ELSE '' END) 主键, b.name 类型, a.length 占用字节数, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, (CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空, ISNULL(e.text, '') 默认值, ISNULL(g.[value], '') AS 字段说明
INTO #tmp
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name<>'dtproperties'
LEFT JOIN syscomments e ON a.cdefault=e.id
LEFT JOIN sys.extended_properties g ON a.id=g.major_id AND a.colid=g.minor_id
-- WHERE d.name LIKE '%Buy_%'
ORDER BY a.id, a.colorder
SELECT (CASE WHEN #tmp.字段序号=1 THEN 表名 ELSE '' END) 表名, (CASE WHEN #tmp.字段序号=1 THEN ISNULL(g.[value], '')ELSE '' END) AS 表说明, #tmp.字段序号, #tmp.字段名, #tmp.标识, #tmp.主键, #tmp.类型, #tmp.占用字节数, #tmp.长度, #tmp.小数位数, #tmp.允许空, #tmp.默认值, #tmp.字段说明
FROM sysobjects obj
--LEFT JOIN syscolumns a ON a.id = obj.id AND obj.xtype = 'U' AND obj.name <> 'dtproperties'
LEFT JOIN sys.extended_properties g ON g.major_id=obj.id
JOIN #tmp ON #tmp.表名=obj.name
WHERE obj.type='U' AND obj.name<>'sysdiagrams' AND g.minor_id=0
DROP TABLE #tmp历史SQL语句记录
SELECT st.text as sql_statement,
qs.creation_time as plan_last_compiled,
qs.last_execution_time as plan_last_executed,
qs.execution_count as plan_executed_count,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE DATEDIFF(SECOND, qs.last_execution_time,GETDATE())<10
order by plan_last_executed desc