Este script permite obter informações como o nome da tabela, colunas, se a coluna aceita nulo ou é PK e outras.
SELECT user_name(obj.uid) as UserName,obj.id as TableID,
obj.name as TableName, col.name as FieldName,
col.colorder as ColumnOrder, type_name(col.xusertype) +
CASE WHEN type_name(col.xusertype) = ‘varchar’
THEN ‘(‘ + convert(varchar,convert(int, prec)) + ‘)’
WHEN type_name(col.xusertype) = ‘nvarchar’
THEN ‘(‘ + convert(varchar,convert(int, prec)) + ‘)’
WHEN type_name(col.xusertype) = ‘char’
THEN ‘(‘ + convert(varchar,convert(int, prec)) + ‘)’
WHEN type_name(col.xusertype) = ‘nchar’
THEN ‘(‘ + convert(varchar,convert(int, prec)) + ‘)’
ELSE
convert(varchar,”)
END AS DataType,
CASE WHEN index_col(obj.name, 1, col.colorder) != ”
THEN ‘Yes’ ELSE ‘No’ END AS IsPK,
convert(varchar, pro.value) as ColumnComments,
CASE col.IsNullable WHEN 1
THEN ‘Yes’ ELSE ‘No’ END AS IsNullable
FROM sysobjects obj
INNER JOIN syscolumns col on obj.id = col.id
LEFT JOIN sysobjects def on obj.id = def.parent_obj and def.info = col.colorder and def.xtype in (‘D ‘)
LEFT JOIN syscomments com on def.id = com.id
LEFT JOIN sysproperties pro on obj.id = pro.id and col.colorder = pro.smallid
left join sysproperties prot on prot.id = obj.id and prot.smallid = 0
WHERE obj.xtype = ‘U’
AND obj.name != ‘dtproperties’
ORDER BY object_name(obj.id), col.colorder
OBS: Resultado semelhante também pode ser obtido usando a view de sistema INFORMATION_SCHEMA.COLUMNS. Para saber mais veja a dica Obtendo informações sobre a estrutura das tabelas.