SELECT 	
'[' + SC.Name  + '] ' +  '[' + ST.NAME + ']' +  
CASE 
WHEN (ST.Name='bigint') THEN ''
WHEN (ST.Name='binary') THEN ' (' + CONVERT(varchar(10), SC.Length)  + ')' 
WHEN (ST.Name='bit') THEN '' 
WHEN (ST.Name='char') THEN ' (' + CONVERT(varchar(10), SC.Length)  + ')' 
WHEN (ST.Name='datetime') THEN '' 
WHEN (ST.Name='decimal') THEN ' (' + CONVERT(varchar(10), SC.Prec) + ', ' + CONVERT(varchar(10), SC.Scale)  + ')' 
WHEN (ST.Name='float') THEN '' 
WHEN (ST.Name='image') THEN '' 
WHEN (ST.Name='int') THEN '' 
WHEN (ST.Name='money') THEN '' 
WHEN (ST.Name='nchar') THEN ' (' + CONVERT(varchar(10), SC.Length)  + ')' 
WHEN (ST.Name='ntext') THEN '' 
WHEN (ST.Name='numeric') THEN '(' + CONVERT(varchar(10), SC.Prec) + ', ' + CONVERT(varchar(10), SC.Scale)  + ')' 
WHEN (ST.Name='nvarchar') THEN '' 
WHEN (ST.Name='real') THEN '' 
WHEN (ST.Name='smalldatetime') THEN '' 
WHEN (ST.Name='smallint') THEN '' 
WHEN (ST.Name='smallmoney') THEN '' 
WHEN (ST.Name='sql_variant') THEN '' 
WHEN (ST.Name='sysname') THEN '' 
WHEN (ST.Name='text') THEN '' 
WHEN (ST.Name='timestamp') THEN '' 
WHEN (ST.Name='tinyint') THEN '' 
WHEN (ST.Name='uniqueidentifier') THEN '' 
WHEN (ST.Name='varbinary') THEN ' (' + CONVERT(varchar(10), SC.Length)  + ')' 
WHEN (ST.Name='varchar') THEN ' (' + CONVERT(varchar(10), SC.Length)  + ')' 

ELSE ' ' END + ' ' + CASE WHEN SC.isnullable = 0 THEN ' NOT NULL ' ELSE ' NULL ' END 
+ ' ' + CASE WHEN SCmnts.text is not null THEN ' DEFAULT ' + SCmnts.text ELSE '' END  AS ColumnScript,
SC.Name, SC.xtype, SC.xusertype, SC.length, SC.xprec, SC.xscale, SC.cdefault, SC.type, SC.usertype, SC.prec, SC.scale, SC.isnullable
FROM SYSCOLUMNS SC
INNER JOIN SYSTYPES ST ON SC.xusertype = ST.xusertype
LEFT JOIN SYSCOMMENTS SCmnts ON SC.cdefault = SCmnts.ID
WHERE SC.ID IN (SELECT ID FROM SysObjects WHERE [NAME] ='sysobjects')