MSSQL SQL功能性代码片段 收藏

查询数据库各个表的行数

SELECT
	a.name AS '表名',
	b.rows AS '表数据行数' 
FROM
	sysobjects a
	INNER JOIN sysindexes b ON a.id = b.id 
WHERE
	a.type = 'u' 
	AND b.indid IN ( 0, 1 ) --and a.name not like 't%'
	
ORDER BY
	b.rows DESC

效果:

查询各个表的表名以及注释

SELECT
	obj.name AS 'tableName',
CASE
		
		WHEN pro.value IS NULL 
		OR pro.value = '' THEN
			obj.name ELSE CONVERT ( NVARCHAR ( MAX ), pro.value ) 
			END AS 'tableDescription' 
	FROM
		( SELECT id, name FROM sys.sysobjects WHERE xtype IN ( 'U' ) -- U：用户创建的表，S：系统自带的表 ('U', 'S')
		) obj
		LEFT JOIN sys.extended_properties pro ON pro.name = 'MS_Description' 
		AND pro.minor_id = 0 
		AND pro.major_id = obj.id 
ORDER BY
	obj.name;

效果: