SQL SERVER根据传入的数据表名,格式化显示表结构相关信息

最新推荐文章于 2022-11-15 18:32:14 发布

沃克蒙尼

最新推荐文章于 2022-11-15 18:32:14 发布

阅读量315

点赞数

分类专栏： SQL SERVER

本文链接：https://blog.csdn.net/Carry_Irving/article/details/84099228

版权

SQL SERVER 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

根据传入的数据表名称, 返回数据表结构相关信息, 比sp_help更直观也减少一些不必要信息的输出;

过程编写参考自:https://www.cnblogs.com/davidhou/p/5842004.html

输出样式:

储存过程代码:

CREATE PROCEDURE sp_GetTableInfoByNames @tablenames varchar(MAX)
AS 
BEGIN
	--MSH:2018年11月15日 根据传入的数据表名称, 显示数据表相应的结构信息
	SELECT /*obj.name, */ CASE WHEN col.colorder = 1 THEN obj.name ELSE '' END AS 表名, col.colorder AS 序号, col.name AS 列名, ISNULL(ep.[value], '') AS 列说明,
		t.name AS 数据类型, col.length AS 长度, 
		CASE WHEN t.name IN ('int', 'decimal') THEN ISNULL(COLUMNPROPERTY(col.id, col.name, 'Precision'), 0)ELSE NULL END AS 数值总长度,
		CASE WHEN t.name IN ('int', 'decimal') THEN ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0)ELSE NULL END 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 IN (SELECT Name FROM dbo.fn_strtotable(@tablenames) ) --表名
		ORDER BY obj.name /*col.colorder*/
END

使用到相关函数将字符串转换为表:

CREATE FUNCTION fn_strtotable(@text TEXT)
RETURNS @tbl TABLE (Name VARCHAR(36))
AS
BEGIN
    DECLARE @IDs VARCHAR(1000)
    DECLARE @S VARCHAR(36)
    DECLARE @B INT, @E INT, @L INT, @N INT, @K INT

    SET @L = DATALENGTH(@text)
    SET @B = 1
    IF @L > 0
    WHILE @B <= @L
    BEGIN
        IF @L - @B > 900 SET @N = 900 ELSE SET @N = @L - @B + 1
        WHILE NOT (SUBSTRING(@text, @B + @N - 1, 1) IN (',', ';', ' ', '(', ')', '''', '"')) AND (@B + @N <= @L) SET @N = @N + 1
        SET @IDs = SUBSTRING(@text, @B, @N)
        SET @B = @B + @N

        SET @IDs = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@IDs)), ';', ','), '(', ','), ')', ','), ' ', ','), '''', ','), '"', ',')
        IF RIGHT(@IDs, 1) <> ',' SET @IDs = @IDs + ','  
        SET @K = CHARINDEX(',', @IDs)                  
        WHILE @K > 0
        BEGIN                 
            SET @S = LEFT(@IDs, @K - 1)
            IF @S <> '' INSERT INTO @tbl VALUES(@S)
            SET @IDs = SUBSTRING(@IDs, @K + 1, LEN(@IDs) - @K)
            SET @K = CHARINDEX(',', @IDs)                  
        END
    END
    RETURN
END