Table Definitions from a Database.

Some times we might need all the information about tables and columns in a Database.

Below scripted Code Defines the properties of all the columns from all tables, Foreign key specifications as well.

One can export the result to an Excel for Database Design overview.

This code works fine in SQL server 2005.

Here we GO...!!!!

/*****Lists all the Primary key and Indexs from all the Table*****/

select so.object_id,
object_name(so.object_id)as tbl_name,
is_unique ,
is_primary_key,
sc.name,
colid ,
i.type_desc
into #primary
from sys.objects so
join sys.columns sc
on sc.object_id=so.object_id
join sysindexkeys ik
on ik.id=so.object_id and ik.colid=sc.column_id
join sys.indexes i
on i.object_id=ik.id and i.index_id=ik.indid
where so.type='U'
order by 1,4

/*****Table to Store basic info for all the tables*****/

Create table #TableList
(
tablename nvarchar(100),
table_id int,
ColumnName nvarchar(100),
column_id int,
DataType nvarchar(100),
max_length int,
[precision] int,
[scale] int,
is_nullable int,
is_identity int,
is_computed int,
default_object_id int,
is_unique int,
is_primary_key int
)
insert into #TableList
select so.name TableName,
so.object_id,
sc.name as ColumnName,
sc.column_id,
sst.name as Datatype,
max_length,
precision,
sc.scale,
is_nullable,
is_identity,
is_computed,
default_object_id,
is_unique=(select is_unique from #primary where tbl_name=so.name and name=sc.name and is_unique=1 and is_primary_key=0),
is_primary_key=(select is_primary_key from #primary where tbl_name=so.name and name=sc.name and is_primary_key=1)
from sys.columns sc
join sys.objects so on sc.object_id=so.object_id
join sys.systypes sst on sc.user_type_id=sst.xusertype
where so.type='U'
ORDER BY object_name(sc.object_id),sc.name

/*****Table to store Foreign key informations.*****/

Create table #ForeignKeys (
[Table_id] int,
TableName nvarchar(100),
Column_id int,
ColumnName nvarchar(100),
ForeignKey nvarchar(100),
ReferenceTableName nvarchar(100),
ReferenceColumnName nvarchar(100),
ReferenceObjectId int,
ReferenceColumnDatatype nvarchar(100),
ReferenceColumnMax_length int
)
Insert into #ForeignKeys
SELECT
f.parent_object_id,
OBJECT_NAME(f.parent_object_id) AS TableName,
fc.Parent_column_id,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
f.name AS ForeignKey,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName,
so.object_id,
sst.name as Datatype,
sc.max_length
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
inner join sys.objects so on f.referenced_object_id = so.object_id
inner join sys.columns sc on f.referenced_object_id = sc.object_id and fc.referenced_column_id = sc.column_id
inner join sys.systypes sst on sc.user_type_id=sst.xusertype
order by OBJECT_NAME(f.parent_object_id)

/*****Displays Details of all the Tables from a database.*****/

Select a.tablename,
a.ColumnName,
a.DataType,
a.max_length,
a.[precision],
a.[scale],
a.is_nullable,
a.is_identity,
a.is_computed,
a.default_object_id,
a.is_unique,
a.is_primary_key,
b.ForeignKey,
b.ReferenceTableName,
b.ReferenceColumnName,
b.ReferenceColumnDatatype,
b.ReferenceColumnMax_length
from #TableList a
left outer join #ForeignKeys b on a.table_id = b.table_id and a.column_id = b.column_id
order by a.Tablename,a.ColumnName

drop table #TableList,#ForeignKeys,#primary

--Thanks to Ram and Karthikeyan for Sharing this...