[DB] 自動產生補描述語法 (Table)

SELECT
'EXEC [dbo].[uspRefreshDescription] ''' + ISNULL(CAST(b.value AS NVARCHAR(255)), '') + ''', ''dbo'', ''' + a.name + '''' AS 'Query',
a.name,
b.value AS 'Description'
FROM sys.tables a
LEFT JOIN fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', NULL, NULL, NULL) b 
ON a.name = b.objname COLLATE Chinese_Taiwan_Stroke_CI_AS
ORDER BY name

--自動產生補描述語法 (Column)
;WITH tableInfo AS (
SELECT a.name AS 'tableName', b.name AS 'columnName'
, (SELECT value FROM fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', a.name, 'column', b.name)) AS 'Description'
FROM sys.tables a
JOIN sys.columns b ON a.object_id = b.object_id
)
SELECT 'EXEC [dbo].[uspRefreshDescription] ''' + ISNULL(CAST(Description AS NVARCHAR(255)), '') + ''', ''dbo'', ''' + tableName + ''', ''' + columnName + '''' AS 'Query', * 
FROM tableInfo
--WHERE tableName = 'sysuser'