SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name",SM.TEXT AS "Default Value"
FROM dbo.sysobjects so
INNER JOIN dbo.syscolumns sc ON so.id = sc.id
LEFT JOIN dbo.syscomments sm ON SC.cdefault = sm.id
WHERE SO.xtype = 'U' and ( sm.text is not null)
ORDER BY so.[name], sc.colid
--带默认约束
SELECT ST.[name] AS "Table Name",
SC.[name] AS "Column Name",
SD.definition AS "Default Value",
SD.[name] AS "Constraint Name"
FROM sys.tables ST INNER
JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id]
INNER JOIN sys.default_constraints SD ON ST.[object_id]
= SD.[parent_object_id] AND SC.colid = SD.parent_column_id
ORDER BY ST.[name], SC.colid
--带主键(key值的包括UNIQUE)
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
SELECT CONSTRAINT_NAME, TABLE_NAME,COLUMN_NAME=STUFF((SELECT ','+[COLUMN_NAME] FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t WHERE CONSTRAINT_NAME=t1.CONSTRAINT_NAME FOR XML PATH('')), 1, 1, '')
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t1
GROUP BY CONSTRAINT_NAME, TABLE_NAME
--无默认约束--无主键(有索引)
select st.object_id,sc.column_id, st.name,sdc.name,sc.name ,ccu.COLUMN_NAME
from sys.all_columns sc
inner join sys.tables st on sc.object_id = st.object_id
left outer join sys.default_constraints sdc on sc.object_id = sdc.parent_object_id
and sc.column_id = sdc.parent_column_id
left outer join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on
sc.object_id = OBJECT_ID(ccu.TABLE_NAME) and
sc.name = ccu.COLUMN_NAME
where sc.user_type_id = 175
and sc.max_length >1 and sdc.name is null and ccu.COLUMN_NAME is null
order by sdc.name
--无默认约束--无主键(无索引)
select 'ALTER TABLE ', st.name, ' ALTER COLUMN ' + sc.name + ' varchar(',sc.max_length,') ' +
case sc.is_nullable when 0 then 'not null' else '' end
from sys.all_columns sc
inner join sys.tables st on sc.object_id = st.object_id
left outer join sys.default_constraints sdc on sc.object_id = sdc.parent_object_id
and sc.column_id = sdc.parent_column_id
left outer join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on
sc.object_id = OBJECT_ID(ccu.TABLE_NAME) and
sc.name = ccu.COLUMN_NAME
left outer join (SELECT a.object_id,a.column_id, c.name,c.is_primary_key
FROM sys.columns a inner join sys.index_columns b
ON a.object_id = b.object_id AND a.column_id = b.column_id inner join sys.indexes c
ON b.object_id = c.object_id AND b.index_id = c.index_id) SI
on sc.object_id = SI.object_id and sc.column_id = SI.column_id
where sc.user_type_id = 175
and sc.max_length >1
and sdc.name is null
and ccu.COLUMN_NAME is null
and SI.name is null
order by sdc.name
--无条件的更新字段char
select st.object_id,sc.column_id, st.name,sdc.name,sc.name
from sys.all_columns sc
inner join sys.tables st on sc.object_id = st.object_id
left outer join sys.default_constraints sdc on sc.object_id = sdc.parent_object_id
and sc.column_id = sdc.parent_column_id
where sc.user_type_id = 175
and sc.max_length >1 and sdc.name is null
order by sdc.name
--更改char为varchar
select 'ALTER TABLE ',
sys.tables.name,
' ALTER COLUMN ',
sys.columns.name,
' varchar(',
sys.columns.max_length,
') ',
case is_nullable
when 0 then 'not null'
else ''
end
from sys.columns,sys.tables
where sys.columns.object_id = sys.tables.object_id and
user_type_id = 175 and
max_length <> 1
order by sys.tables.name