【SqlServer】存储过程：批量查询数据库下表的元数据

批量查询数据库下表的元数据"/>

【SqlServer】存储过程：批量查询数据库下表的元数据

一、查询单张表

1.1 根据表名查询表结构

--快速查看表结构(比较全面的)
DECLARE @tableName NVARCHAR(MAX);
SET @tableName = N'YMUS'; --表名!!!SELECT CASEWHEN col.colorder = 1 THENobj.nameELSEobj.nameEND AS 表名,col.colorder AS 序号,col.name AS 列名,ISNULL(ep.[value], '') AS 列说明,t.name AS 数据类型,col.length AS 长度,ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数,CASEWHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN'√'ELSE''END AS 标识,CASEWHEN EXISTS(SELECT 1FROM dbo.sysindexes siINNER JOIN dbo.sysindexkeys sikON si.id = sik.idAND si.indid = sik.indidINNER JOIN dbo.syscolumns scON sc.id = sik.idAND sc.colid = sik.colidINNER JOIN dbo.sysobjects soON so.name = si.nameAND so.xtype = 'PK'WHERE sc.id = col.idAND sc.colid = col.colid) THEN'√'ELSE''END AS 主键,CASEWHEN col.isnullable = 1 THEN'√'ELSE''END AS 允许空,ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns colLEFT JOIN dbo.systypes tON col.xtype = t.xusertypeINNER JOIN dbo.sysobjects objON col.id = obj.idAND obj.xtype = 'U'AND obj.status >= 0LEFT JOIN dbo.syscomments commON col.cdefault = comm.idLEFT JOIN sys.extended_properties epON col.id = ep.major_idAND col.colid = ep.minor_idAND ep.name = 'MS_Description'LEFT JOIN sys.extended_properties epTwoON obj.id = epTwo.major_idAND epTwo.minor_id = 0AND epTwo.name = 'MS_Description'
WHERE obj.name = @tableName --表名
ORDER BY col.colorder;

1.2 存储过程封装

IF OBJECT_ID('er1.export_result_records', 'P') IS NOT NULLDROP PROCEDURE er1.export_result_records;
GO
--快速查看表结构(比较全面的)
CREATE PROCEDURE er1.export_result_records
@tableName NVARCHAR(MAX)
AS
BEGINDELETE FROM er1.a01_table_info WHERE CAST(表名 AS nvarchar(max)) = @tableName ;
INSERT INTO er1.a01_table_info(表名,序号,列名,列说明,数据类型,长度,小数位数,标识,主键,允许空,默认值)
SELECT CASEWHEN col.colorder = 1 THENobj.nameELSEobj.nameEND AS 表名,col.colorder AS 序号,col.name AS 列名,ISNULL(ep.[value], '') AS 列说明,t.name AS 数据类型,col.length AS 长度,ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数,CASEWHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN'√'ELSE''END AS 标识,CASEWHEN EXISTS(SELECT 1FROM dbo.sysindexes siINNER JOIN dbo.sysindexkeys sikON si.id = sik.idAND si.indid = sik.indidINNER JOIN dbo.syscolumns scON sc.id = sik.idAND sc.colid = sik.colidINNER JOIN dbo.sysobjects soON so.name = si.nameAND so.xtype = 'PK'WHERE sc.id = col.idAND sc.colid = col.colid) THEN'√'ELSE''END AS 主键,CASEWHEN col.isnullable = 1 THEN'√'ELSE''END AS 允许空,ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns colLEFT JOIN dbo.systypes tON col.xtype = t.xusertypeINNER JOIN dbo.sysobjects objON col.id = obj.idAND obj.xtype = 'U'AND obj.status >= 0LEFT JOIN dbo.syscomments commON col.cdefault = comm.idLEFT JOIN sys.extended_properties epON col.id = ep.major_idAND col.colid = ep.minor_idAND ep.name = 'MS_Description'LEFT JOIN sys.extended_properties epTwoON obj.id = epTwo.major_idAND epTwo.minor_id = 0AND epTwo.name = 'MS_Description'
WHERE obj.name = @tableName --表名
ORDER BY col.colorder;
END

二、循环遍历所有表

IF OBJECT_ID('er1.cursor_loop_achive_tbname', 'P') IS NOT NULLDROP PROCEDURE er1.cursor_loop_achive_tbname;
GO
CREATE PROCEDURE er1.cursor_loop_achive_tbname
AS
BEGINSET NOCOUNT ON;DECLARE @table_name nvarchar(128),@sql nvarchar(max);-- 声明游标，并以系统表 sys.tables 作为查询对象DECLARE table_cursor CURSOR FORSELECT name FROM sys.tables;-- 打开游标OPEN table_cursor;-- 依次遍历查询结果，将表名输出FETCH NEXT FROM table_cursor INTO @table_name;WHILE @@FETCH_STATUS = 0BEGINEXEC	[er1].[export_result_records] @tableName = @table_nameFETCH NEXT FROM table_cursor INTO @table_name;ENDCLOSE table_cursor;DEALLOCATE table_cursor;END;

三、存储过程调用

-- 方式1
USE [ER1]
GO
DECLARE	@return_value int
EXEC	@return_value = [er1].[export_result_records]@tableName = N'YMUS'
SELECT	'Return Value' = @return_value
GO-- 方式2
EXEC	[er1].[export_result_records]@tableName = N'YMUS'