Cacher is the code snippet organizer for pro developers

SELECT d.name,'Columns',a.name ColumnName,b.name + replace(replace(replace(replace(isnull(convert(nvarchar, CASE WHEN (charindex('nvarchar',b.name)= 0) THEN NULL ELSE COLUMNPROPERTY(a.id,a.name,'PRECISION') end ),''),'-1','(max)'),'100','(100)'),'1200','(1200)'),'2000','(2000)')+' '+
(case when a.isnullable=1 then 'NULL' else 'NOT NULL' end) CanNull 
 FROM  syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id 
Left join sys.extended_properties f on d.id=f.class and f.minor_id=0 
where b.name is not NULL  And d.name in('SS_Sessions','SS_Respondents','SS_Replies','SS_Questions','SS_Profiles','SS_Options','SS_Lecturers','SS_Dimensions','SS_Definitons','SS_Configs')
order by  d.name

with Tab1 as(
SELECT a.name, count(b.name) AS comCounbt
FROM sysobjects AS a INNER JOIN syscolumns AS b ON a.id = b.id
WHERE (a.type = 'u') 
And a.name like 'SS_%'
Group BY  a.name),
Tab2 as(
select ftable.name ptablename,COunt(fk.name) AS fkCount from sysforeignkeys  
join sysobjects fk    
on sysforeignkeys.constid = fk.id  
join sysobjects ftable  
on sysforeignkeys.fkeyid = ftable.id  
join sysobjects rtable  
on sysforeignkeys.rkeyid = rtable.id  
join syscolumns cn  
on sysforeignkeys.fkeyid = cn.id and sysforeignkeys.fkey = cn.colid
WHERE  ftable.name like 'SS_%'
GROUP BY ftable.name
),
Tab3 As(
select rtable.name ftablename,COunt(fk.name) AS FromFkCount from sysforeignkeys  
join sysobjects fk    
on sysforeignkeys.constid = fk.id  
join sysobjects ftable  
on sysforeignkeys.fkeyid = ftable.id  
join sysobjects rtable  
on sysforeignkeys.rkeyid = rtable.id  
join syscolumns cn  
on sysforeignkeys.fkeyid = cn.id and sysforeignkeys.fkey = cn.colid
WHERE  rtable.name like 'SS_%'
GROUP BY rtable.name
),
TAB4 AS (
SELECT a.name,Count(b.name) AS IndexCount
 FROM 
 sysobjects AS a 
 INNER JOIN sysindexes AS b ON a.id = b.id
 WHERE (a.type = 'u') 
And a.name like 'SS_%'
Group BY  a.name
)
SELECT Tab1.name,Tab1.comCounbt,isnull(TAB4.IndexCount,0) AS TAB4,isnull(Tab2.fkCount,0) AS refersto,ISNULL(Tab3.FromFkCount,0) AS FromFkCount
FROM Tab1
LEFT JOIN Tab2
ON Tab1.name=Tab2.ptablename
LEFT JOIN Tab3
ON Tab1.name=Tab3.ftablename
LEFT JOIn TAB4
ON Tab1.name=TAB4.name
ORDER BY Tab1.name