IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.wm_procedures2') AND type = N'P')

DROP PROC dbo.wm_procedures2

GO

CREATE PROC dbo.wm_procedures2

(

@ObjectName sysname

)

AS

SELECT

o.name AS ObjectName,

t.name AS TableName,

c.name AS ColumnName,

tp.name +

CASE c.is_identity

WHEN 1 THEN ' identity'

ELSE ''

END AS ColumnType,

CASE

WHEN tp.name IN ('ntext','nchar','nvarchar')

THEN c.max_length / 2

ELSE c.max_length

END AS ColumnSize,

dc.definition AS DefaultValue,

CASE

WHEN c.precision = 0 AND c.scale = 0 -- text fields

THEN ''

WHEN tp.name IN ('bit','datetime','smalldatetime')

THEN ''

ELSE CAST(c.precision AS varchar) +

CASE c.scale

WHEN 0

THEN ''

ELSE ' (' + CAST(c.scale AS varchar) + ')'

END

END AS [Digits (Decimals)],

CASE c.is_nullable

WHEN 1 THEN 'Yes'

ELSE 'No'

END AS Nullable,

CASE c.is_computed

WHEN 1 THEN 'Yes'

ELSE 'No'

END AS Computed,

CASE d.is_selected

WHEN 1 THEN 'Yes'

ELSE 'No'

END AS Selected

FROM

sys.sql_dependencies d

INNER JOIN sys.objects o

ON d.object_id = o.object_id

INNER JOIN sys.tables t

ON d.referenced_major_id = t.object_id

INNER JOIN sys.columns c

ON t.object_id = c.object_id

AND d.referenced_minor_id = c.column_id

INNER JOIN sys.types tp

ON c.system_type_id = tp.user_type_id

LEFT JOIN sys.default_constraints dc

ON c.default_object_id = dc.object_id

WHERE

o.type = 'P'

AND o.object_id = OBJECT_ID(@ObjectName)

ORDER BY

1,2,3

GO