出自ProgWiki
用途
- 無參數時, List所有的Table名稱。
- 有參數時, List特定Table裡的欄位定義。
程式碼
--物件: StoredProcedure [dbo].[MyTable]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MyTable](
@strTableName AS NVARCHAR(100) = null
)
AS
BEGIN
IF @strTableName IS NULL
BEGIN
SELECT
TABLE名稱=name
,建立日期=crdate
FROM sysobjects
WHERE type='U' AND SUBSTRING (name,1,6) NOT IN ('aspnet')
ORDER BY crdate DESC;
END
ELSE
BEGIN
SELECT
--序號
序號=a.colorder
--欄位名稱
,欄位名稱=a.name
--資料類型
,資料類型=b.name
+CASE
WHEN (b.name in ('nchar', 'nvarchar')) THEN
'(' + CASE WHEN (a.length = -1) THEN 'max' ELSE LTRIM(STR(a.length / 2)) END + ')'
WHEN (b.name in ('binary','char', 'varchar')) THEN
'(' + CASE WHEN (a.length = -1) THEN 'max' ELSE LTRIM(STR(a.length)) END + ')'
WHEN (b.name in ('decimal','numeric')) THEN
'('+CONVERT(VARCHAR(10),a.xprec)+','+CONVERT(VARCHAR(10),a.xscale)+')'
ELSE
''
END
--實際長度_Byte -- col_length(@strTableName,a.name)
--,實際長度_Byte=a.length
--索引Key
,索引KEY=
CASE WHEN a.name IN (
SELECT 機碼=d.name
FROM Syscolumns d
INNER JOIN sysobjects e ON d.Id=e.Id AND e.Xtype='U' AND e.Name<>'Dtproperties'
WHERE EXISTS(
SELECT 1 FROM sysobjects WHERE Xtype='Pk' AND name IN (
SELECT name FROM sysindexes WHERE Indid IN (
SELECT Indid FROM sysindexkeys WHERE Id = d.Id AND Colid=d.Colid
)
)
)
AND e.name=c.name
)
THEN 'Y' ELSE '' END
--允許空值
,允許空值=
CASE WHEN a.isnullable IN ( 1 )
THEN 'Y'
ELSE 'N'
END
--預設值
,預設值=
CASE WHEN (a.cdefault <> 0) THEN
(SELECT d.TEXT FROM [syscomments] d WHERE d.id = a.cdefault)
ELSE
''
END
--描述
,描述=
isnull(
(SELECT TOP 1 VALUE
FROM FN_LISTEXTENDEDPROPERTY(NULL,'user','dbo','table',@strTableName,'column',null)
WHERE objname=a.name COLLATE Chinese_Taiwan_Stroke_CI_AS), '')
FROM
[syscolumns] a,
[systypes] b,
[sysobjects] c
WHERE
a.id=c.id
AND a.xtype=b.xtype
AND c.type='U'
AND b.name<>'sysname'
AND c.name=@strTableName
ORDER BY a.colid
END
END
相關
