IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.wm_procedures') AND type = N'P')
DROP PROC dbo.wm_procedures
GO
CREATE PROC dbo.wm_procedures
(
@ObjectName sysname
)
AS
SELECT
@ObjectName AS
ObjectName,
(
SELECT
[name]
FROM
sys.tables
WHERE
object_id
= d.referenced_major_id
) AS TableName,
(
SELECT
[name]
FROM
sys.columns
WHERE
object_id
= d.referenced_major_id
AND column_id =
d.referenced_minor_id
) AS ColumnName,
(
SELECT
[name]
FROM
sys.types
WHERE
system_type_id =
(
SELECT
[system_type_id]
FROM
sys.columns
WHERE
object_id
= d.referenced_major_id
AND column_id =
d.referenced_minor_id
)
AND [name] <> 'sysname'
) + ' ( ' +
(
SELECT
CASE
WHEN
(
SELECT
[name]
FROM
sys.types
WHERE
system_type_id =
(
SELECT
[system_type_id]
FROM
sys.columns
WHERE
object_id = d.referenced_major_id AND
column_id = d.referenced_minor_id
)
AND [name] <> 'sysname'
)
IN
( 'ntext','nchar','nvarchar' )
THEN
CAST([max_length]
/ 2 AS varchar)
ELSE
CAST([max_length] AS varchar)
END
FROM
sys.columns
WHERE
object_id
= d.referenced_major_id
AND column_id =
d.referenced_minor_id
) + ' ) '
AS
ColumnType
FROM
sys.sql_dependencies
d
WHERE
d.object_id = OBJECT_ID(@ObjectName)
AND (SELECT [name] FROM sys.tables WHERE object_id = d.referenced_major_id)
IS NOT NULL
ORDER BY
1, 2, 3
GO