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

一、查询单张表

1.1 根据表名查询表结构

--快速查看表结构（比较全面的）
DECLARE @tableName NVARCHAR(MAX);
SET @tableName = N'YMUS'; --表名!!!

SELECT CASE
           WHEN col.colorder = 1 THEN
               obj.name
           ELSE
               obj.name
       END 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 小数位数,
       CASE
           WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN
               '√'
           ELSE
               ''
       END AS 标识,
       CASE
           WHEN EXISTS
                (
                    SELECT 1
                    FROM dbo.sysindexes si
                        INNER JOIN dbo.sysindexkeys sik
                            ON si.id = sik.id
                               AND si.indid = sik.indid
                        INNER JOIN dbo.syscolumns sc
                            ON sc.id = sik.id
                               AND sc.colid = sik.colid
                        INNER JOIN dbo.sysobjects so
                            ON so.name = si.name
                               AND so.xtype = 'PK'
                    WHERE sc.id = col.id
                          AND sc.colid = col.colid
                ) THEN
               '√'
           ELSE
               ''
       END AS 主键,
       CASE
           WHEN col.isnullable = 1 THEN
               '√'
           ELSE
               ''
       END AS 允许空,
       ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
    LEFT JOIN dbo.systypes t
        ON col.xtype = t.xusertype
    INNER JOIN dbo.sysobjects obj
        ON col.id = obj.id
           AND obj.xtype = 'U'
           AND obj.status >= 0
    LEFT JOIN dbo.syscomments comm
        ON col.cdefault = comm.id
    LEFT JOIN sys.extended_properties ep
        ON col.id = ep.major_id
           AND col.colid = ep.minor_id
           AND ep.name = 'MS_Description'
    LEFT JOIN sys.extended_properties epTwo
        ON obj.id = epTwo.major_id
           AND epTwo.minor_id = 0
           AND epTwo.name = 'MS_Description'
WHERE obj.name = @tableName --表名
ORDER BY col.colorder;

1.2 存储过程封装

IF OBJECT_ID('er1.export_result_records', 'P') IS NOT NULL
	DROP PROCEDURE er1.export_result_records;
GO
--快速查看表结构（比较全面的）
CREATE PROCEDURE er1.export_result_records
@tableName NVARCHAR(MAX)
AS
BEGIN

DELETE FROM er1.a01_table_info WHERE CAST(表名 AS nvarchar(max)) = @tableName ;
INSERT INTO er1.a01_table_info(表名,序号,列名,列说明,数据类型,长度,小数位数,标识,主键,允许空,默认值)
SELECT CASE
           WHEN col.colorder = 1 THEN
               obj.name
           ELSE
               obj.name
       END 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 小数位数,
       CASE
           WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN
               '√'
           ELSE
               ''
       END AS 标识,
       CASE
           WHEN EXISTS
                (
                    SELECT 1
                    FROM dbo.sysindexes si
                        INNER JOIN dbo.sysindexkeys sik
                            ON si.id = sik.id
                               AND si.indid = sik.indid
                        INNER JOIN dbo.syscolumns sc
                            ON sc.id = sik.id
                               AND sc.colid = sik.colid
                        INNER JOIN dbo.sysobjects so
                            ON so.name = si.name
                               AND so.xtype = 'PK'
                    WHERE sc.id = col.id
                          AND sc.colid = col.colid
                ) THEN
               '√'
           ELSE
               ''
       END AS 主键,
       CASE
           WHEN col.isnullable = 1 THEN
               '√'
           ELSE
               ''
       END AS 允许空,
       ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
    LEFT JOIN dbo.systypes t
        ON col.xtype = t.xusertype
    INNER JOIN dbo.sysobjects obj
        ON col.id = obj.id
           AND obj.xtype = 'U'
           AND obj.status >= 0
    LEFT JOIN dbo.syscomments comm
        ON col.cdefault = comm.id
    LEFT JOIN sys.extended_properties ep
        ON col.id = ep.major_id
           AND col.colid = ep.minor_id
           AND ep.name = 'MS_Description'
    LEFT JOIN sys.extended_properties epTwo
        ON obj.id = epTwo.major_id
           AND epTwo.minor_id = 0
           AND epTwo.name = 'MS_Description'
WHERE obj.name = @tableName --表名
ORDER BY col.colorder;
END

二、循环遍历所有表

IF OBJECT_ID('er1.cursor_loop_achive_tbname', 'P') IS NOT NULL
	DROP PROCEDURE er1.cursor_loop_achive_tbname;
GO
CREATE PROCEDURE er1.cursor_loop_achive_tbname
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @table_name nvarchar(128),
          @sql nvarchar(max);

  -- 声明游标，并以系统表 sys.tables 作为查询对象
  DECLARE table_cursor CURSOR FOR
  SELECT name FROM sys.tables;

  -- 打开游标
  OPEN table_cursor;
  
  -- 依次遍历查询结果，将表名输出
  FETCH NEXT FROM table_cursor INTO @table_name;
  WHILE @@FETCH_STATUS = 0
  BEGIN
	EXEC	[er1].[export_result_records] @tableName = @table_name
    FETCH NEXT FROM table_cursor INTO @table_name;
  END
  
  CLOSE 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'