這只是筆記本

要更新整個資料庫內特定欄位的屬性，很多Table又都需要調整時，一個一個看再改很辛苦，
整批列出來在下where條件篩選比較方便。

SELECT  a.name AS TableName,
b.colorder AS 欄位順序,
b.name AS 欄位名稱,
c.name + ” AS 欄位型態,
b.prec AS 欄位長度,
b.isnullable AS iIsNull ,
ISNULL(pk.CONSTRAINT_TYPE, ”) AS PRIMARY_KEY,
ISNULL(fk.CONSTRAINT_TYPE, ”) AS FOREIGN_KEY,
d.value AS 說明 ,
‘ALTER TABLE dbo.[‘+a.name+’] ALTER Column [‘+b.name+’] ‘+c.name+'(6) ‘+case b.isnullable when 0 then ‘NOT NULL’ else ‘NULL’ end AS 改長度語法
FROM    dbo.sysobjects a
INNER JOIN dbo.syscolumns b
ON a.id = b.id
INNER JOIN dbo.systypes c
ON b.xusertype = c.xusertype
LEFT JOIN ::fn_listextendedproperty(NULL, ‘schema’, ‘dbo’, ‘table’, replace(‘@TableName’,’dbo.’,”) , ‘column’, NULL) d
ON d.objname = b.name collate Chinese_Taiwan_Stroke_CI_AS
LEFT OUTER JOIN
(
select
pk.TABLE_NAME,
c.COLUMN_NAME,
pk.CONSTRAINT_TYPE
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
on
c.TABLE_NAME = pk.TABLE_NAME and
c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
) as pk
ON a.name = pk.TABLE_NAME and
b.name = pk.COLUMN_NAME and
pk.CONSTRAINT_TYPE = ‘PRIMARY KEY’
LEFT OUTER JOIN
(
select
pk.TABLE_NAME,
c.COLUMN_NAME,
pk.CONSTRAINT_TYPE
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
on
c.TABLE_NAME = pk.TABLE_NAME and
c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
) as fk
ON
a.name = fk.TABLE_NAME and
b.name = fk.COLUMN_NAME and
fk.CONSTRAINT_TYPE = ‘FOREIGN KEY’
WHERE   a.xtype = ‘U’
and b.prec <= 6 and c.name in (‘varchar’,’nvarchar’,’char’,’nchar’)