Script to DB Schema

Create                PROC [dbo].[GetTableSchema_All] 

AS
SET NOCOUNT ON

DECLARE @count int, @tableName varchar(50)

DECLARE @AllTables TABLE  
(RowID int identity,
 TableNamevarchar(50)
)

INSERT @AllTables(TableName)
SELECT   o.name 
FROM sysobjects o
WHERE o.xtype = 'U' 

SET @count = @@ROWCOUNT

DECLARE @table  TABLE   
(tblIDvarchar(50),
TableNamevarchar(100),
ColumnNamevarchar(50),
 DataTypevarchar(25),
 ColumnLengthint, 
ColIdint,
Captionvarchar(100)

)


WHILE @count > 0
BEGIN
SELECT @tableName = TableName
FROM @AllTables
WHERE RowID = @count

INSERT @table(tblID,
TableName,
ColumnName,
 DataType,
 ColumnLength,
ColId)
SELECT   o.id, o.name TableName,
 c.name ColumnName, 
 t.name,c.length ColumnLength, colid
FROM sysobjects o
JOIN syscolumns c
ON o.id = c.id
JOIN systypes t
ON c.xtype = t.xtype

WHERE o.xtype = 'U'
AND o.name = @tableName
ANDt.name not in('sysname')
ORDER BY ColumnName

UPDATE t
SET Caption  = CONVERT(varchar(100),value)
FROM @table t
JOIN (SELECT   objname, value
FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @tableName, 'column', default)) f
ON t.ColumnName = f.objname


SET @count = @count - 1
END

SELECT tblID,
TableName,
ColumnName,
 DataType,
 ColumnLength,
ColId,
Caption
FROM @table
ORDER BY TableName, ColId



SELECT TableName, ColumnName, d.ColId, convert(varchar(225),text) Constraints,
CASE xtype WHEN 'C' THEN 'Check' 
WHEN 'D' THEN 'Default'
END ConstraintType,NULL RefTableName, NULL RefColumnName, NULL CnstIsDisabled,NULL CnstIsNotRepl,
NULL CnstIsDeleteCascade,NULL CnstIsUpdateCascade
FROM syscomments c 
JOIN (SELECT  TableName, ColumnName,ColId, id,xtype
FROM sysobjects o
JOIN  @table t
ON parent_obj =  tblID
AND (o.info = t.ColId)
) d
ON c.id = d.id

UNION 

SELECT TableName, NULL, NULL, convert(varchar(250),text ), 'Check Table Level', NULL,NULL,NULL,NULL,NULL,NULL
FROM syscomments c 
JOIN (SELECT distinct tblID, TableName, id
FROM sysobjects o 
JOIN  @table t
ON parent_obj =  tblID 
AND o.info = 0
AND xtype = 'C'
) o
ON o.id = c.id
UNION
SELECT TableName, ColumnName, ColId, o.name,'FK',
CONVERT(varchar(150),'REFERENCES ' + db_name() + '.' + rtrim(user_name(ObjectProperty(rkeyid,'ownerid')))
+ '.' + object_name(rkeyid))  RefTableName,  
CONVERT(varchar(50),col_name(rkeyid,k.rkey))  RefColumnName,
ObjectProperty(k.constid, 'CnstIsDisabled') CnstIsDisabled,
ObjectProperty(k.constid, 'CnstIsNotRepl') CnstIsNotRepl,
ObjectProperty(k.constid, 'CnstIsDeleteCascade') CnstIsDeleteCascade,
ObjectProperty(k.constid, 'CnstIsUpdateCascade') CnstIsUpdateCascade

FROM @table t
JOIN dbo.sysforeignkeys k
ON t.tblID = k.fkeyid
AND k.fkey = t.ColId
JOIN dbo.sysobjects  o
ON t.tblID = o.parent_obj
AND (k.constid = o.id)
WHERE xtype = 'F'
UNION
SELECT TableName, c.name ColumnName,c.colid, o.name, 'PK',NULL,NULL,NULL,NULL,NULL,NULL
from sysindexkeys k
JOIN sysobjects o 
 ON k.id = o.parent_obj
and o.xtype = 'PK' 
JOIN syscolumns c
on c.id = k.id
 AND 
c.colid = k.colid

JOIN (SELECT distinct tblID, TableName FROM @table) t
ON t.tblID = k.id
WHERE k.indid = 1 
ORDER BY TableName, ColId