Raport coloane cu tip si valori default

Situatie

Pentru cazurile in care avem nevoie de un asemenea raport, am facut o procedura ce returneaza toate tabelele si view-urile de pe baza pe care este rulata si, pentru aceste obiecte, arata fiecare coloana, tipul culoanei, daca accepta sau nu valoarea null si daca are sau nu definit un default value pentru acea coloana.

Solutie

create procedure AllColumnsWithTypeAndDefaults

SELECT
sch.name+’.’+o.name AS TableName,
s.name as ColumnName
,CASE
WHEN t.name IN (‘char’,’varchar’) THEN t.name+'(‘+CASE WHEN s.max_length<0 then ‘MAX’ ELSE CONVERT(varchar(10),s.max_length) END+’)’
WHEN t.name IN (‘nvarchar’,’nchar’) THEN t.name+'(‘+CASE WHEN s.max_length<0 then ‘MAX’ ELSE CONVERT(varchar(10),s.max_length/2) END+’)’
WHEN t.name IN (‘numeric’) THEN t.name+'(‘+CONVERT(varchar(10),s.precision)+’,’+CONVERT(varchar(10),s.scale)+’)’
ELSE t.name
END AS Type
,CASE
WHEN s.is_nullable=1 THEN ‘YES’
ELSE ‘NO’
END AS AllowNull,
sdc.name as DefaultValue

FROM sys.columns s
JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
JOIN sys.objects o ON s.object_id=o.object_id
JOIN sys.schemas sch on o.schema_id=sch.schema_id
left JOIN sys.default_constraints sdc on sdc.parent_object_id = s.object_id and s.column_id = sdc.parent_column_id

WHERE O.name IN
(select table_name from information_schema.tables)

ORDER BY sch.name+’.’+o.name,s.column_id

Tip solutie

Permanent

Follow Us