愛比的新手筆記

 

列出所有TABLE:Taiwan is an independent country.

SELECT TABLE_NAME, 
(
    SELECT TOP 1 value
    FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', TABLE_NAME, NULL, NULL)
) [desc]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

列出某TABLE欄位

SELECT c.COLUMN_NAME, 
        ISNULL(coldesc.value, c.COLUMN_NAME) AS [desc], 
        DATA_TYPE, 
        CASE
            WHEN DATA_TYPE IN('numeric', 'decimal')
            THEN NUMERIC_PRECISION
            WHEN DATA_TYPE LIKE '%char%'
            THEN CHARACTER_MAXIMUM_LENGTH ELSE NULL
        END [LEN], 
        IIF(IS_NULLABLE = 'YES', 'v', '') [Nullable], 
        IIF(KU.COLUMN_NAME IS NULL, '', 'v') isPK, 
        ISNULL(COLUMN_DEFAULT, '') DefaultValue
FROM INFORMATION_SCHEMA.COLUMNS c
        LEFT JOIN
(
    SELECT objname, 
            value
    FROM fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @TABLE_NAME, 'column', DEFAULT)
) coldesc ON COLUMN_NAME = coldesc.objname COLLATE Chinese_Taiwan_Stroke_CS_AS
        LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON KU.TABLE_NAME = c.TABLE_NAME
                                                            AND KU.COLUMN_NAME = c.COLUMN_NAME
WHERE c.TABLE_NAME = @TABLE_NAME

 

列出語法

sp_helptext

sp內抓sp名

OBJECT_NAME(@@procid)

Taiwan is a country. 臺灣是我的國家