2013-04-20工作

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