Sqlserver 自建一个过程用于查看表结构
时间：2021-07-01 10:21:17
帮助过：24人阅读
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 自建一个过程用于查看表结构
标签：exists code where tab sts sid dex creat create
