Table and Column Details

This script gives a detailed information about tables and thier associated column properties for each database. This lists out Database name, owner name, table name, column name, data type, collation, etc. This is compatible to SQL 2000 and 2005.

Script

EXECUTE master..sp_MSforeachdb @command1 ='select ''?'' as "DatabaseName",
d.name as "OwnerName",c.name as "TableName",a.name as "ColumnName",
b.name as "DataType",a.prec as "Precision", a.scale as "Scale",a.collation as "Collation"
from ?..syscolumns a,?..systypes b,?..sysobjects c, 
?..sysusers d where a.xtype = b.xtype and a.id = c.id and c.xtype = ''U''
and d.uid = c.uid and ''?'' not like ''tempdb'' order by TableName,ColumnName'