首页 > 代码库 > Sqlserver 自建一个过程用于查看表结构
Sqlserver 自建一个过程用于查看表结构
自建一个过程用于查看表结构
CREATE PROC [sp_tableHelp] (@TableName VARCHAR(128) = ‘‘ ,@ColName VARCHAR(128)=‘‘) AS BEGIN --表结构速查 --Brin --DECLARE @TableName VARCHAR(128) --DECLARE @ColName VARCHAR(128) --SET @TableName = ‘tLocatorMove‘ --SET @ColName = ‘nStatus‘ SELECT tableName = (CASE WHEN col.colorder = 1 OR @TableName IS NULL THEN obj.name ELSE ‘‘ END ) ,colOrder = col.colorder ,colName = col.name ,colDescription = ISNULL(ep.value,‘‘) ,colType = t.name ,colLength = col.length ,colScale = ISNULL(COLUMNPROPERTY(col.id, col.name, ‘Scale‘), 0) ,colIsIdentity = (CASE WHEN COLUMNPROPERTY(col.id, col.name, ‘IsIdentity‘) = 1 THEN ‘√‘ ELSE ‘‘ END) ,colIsPK = ( CASE WHEN EXISTS ( SELECT 1 FROM sys.sysindexes si JOIN sys.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid JOIN sys.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid JOIN sys.sysobjects so ON so.name = si.name AND so.xtype = ‘PK‘ WHERE sc.id = col.id AND sc.colid = col.colid ) THEN ‘√‘ ELSE ‘‘ END ) ,colIsNull = (CASE WHEN col.isnullable = 1 THEN ‘√‘ ELSE ‘‘ END ) ,colDefaultValue = ISNULL(comm.text, ‘‘) FROM sys.syscolumns col LEFT JOIN sys.systypes t ON col.xtype = t.xusertype INNER JOIN sys.sysobjects obj ON col.id = obj.id AND obj.xtype = ‘U‘ AND obj.status >= 0 AND (obj.name = @TableName OR ISNULL(@TableName,‘‘)=‘‘ ) LEFT JOIN sys.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 1=1 AND (col.name = @ColName OR ISNULL(@ColName,‘‘)=‘‘) ORDER BY obj.name,col.colorder; END
Sqlserver 自建一个过程用于查看表结构
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。