SqlServer用sql查询元数据详细的常用操作

SqlServer的元数据查询不需要像Mysql那样语句中指定库，只需要用连接工具连接到希望操作的库的模式下就可以了

--查询表注释
SELECT 
	DISTINCT d.name,
	f.value
FROM
	syscolumns a
LEFT JOIN systypes b ON a.xusertype= 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
LEFT JOIN sys.extended_properties f ON d.id= f.major_id
	AND f.minor_id= 0
	
--查询更加详细的信息
SELECT 
	d.name 表名,
	o.value 表备注,
	a.colorder 字段序号,
	a.name 字段名,
	(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'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 '1' 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 '1'else '' end) 是否允许空,
	isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [字段说明]
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
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
left join (
	-- 出表注释语句
	SELECT 
		DISTINCT d.name,
		f.value
	FROM
		syscolumns a
	LEFT JOIN systypes b ON a.xusertype= 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
	LEFT JOIN sys.extended_properties f ON d.id= f.major_id
		AND f.minor_id= 0
) o on d.name = o.name
where b.name is not null
--WHERE d.name='要查询的表' 如果只查询指定表,用此条件
order by a.id,a.colorder

--查询所有表的数据行数以及数据量大小
SELECT
	a.name,
	b.rows,
	c.used
FROM         
	sys.sysobjects AS a 
INNER JOIN sys.sysindexes AS b ON a.id = b.id
INNER join (
	--里面的这个是出更加详细的空间占用情况的有需要可以单独拉出来用，我内连接这个语句只是为了出used
	SELECT
		OBJECT_NAME(id) AS tablename,
		8 * reserved / 1024 AS reserved,
		RTRIM(8 * dpages) + 'kb' AS used,
		8 * (reserved - dpages) / 1024 AS unused,
        8 * dpages / 1024 - rows / 1024 * minlen / 1024 AS free
	FROM sys.sysindexes
	WHERE (indid = 1)
) c on a.name=c.tablename
WHERE     (b.indid IN (0, 1)) AND (a.type = 'u')
ORDER BY a.id, a.name, b.rows DESC

--查询所有的库名
SELECT Name FROM Master..SysDatabases ORDER BY Name

--只查询表名
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name