To List all the object Dependencies for the particular database

DECLARE @TableList table(TableID INT identity(1,1)

PRIMARY KEY, table_name VARCHAR(256));

DECLARE @EProp table

      (EPropID INT identity(1,1)

      ,TABLE_CATALOG varchar(250)

      ,TABLE_SCHEMA varchar(50)

      ,TABLE_NAME varchar(250)

      ,COLUMN_NAME varchar(250)

      ,ORDINAL_POSITION INT

      ,COLUMN_DEFAULT varchar(80)

      ,IS_NULLABLE varchar(80)

      ,DATA_TYPE varchar(80)

      ,CHARACTER_SET_NAME VARCHAR(100)

      ,COLLATION_NAME varchar(100)

      , col_desc varchar(400));

DECLARE @tableName varchar(250)

INSERT INTO @TableList(table_name)

SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' ORDER BY table_name

DECLARE @maxI INT

DECLARE @I INT

SELECT @i = 1, @maxI = MAX(TableID) FROM @TableList

WHILE @I <= @maxI

BEGIN

      SELECT @tableName = table_name from @TableList where TableID = @i

      INSERT INTO @EProp

            (TABLE_CATALOG

            ,TABLE_SCHEMA

            ,TABLE_NAME

            ,COLUMN_NAME

            ,ORDINAL_POSITION

            ,COLUMN_DEFAULT

            ,IS_NULLABLE

            ,DATA_TYPE

            ,CHARACTER_SET_NAME

            ,COLLATION_NAME

            ,COL_DESC)

      SELECT

            col.TABLE_CATALOG

            ,col.TABLE_SCHEMA

            ,col.TABLE_NAME

            ,col.COLUMN_NAME

            ,col.ORDINAL_POSITION

            ,col.COLUMN_DEFAULT

            ,col.IS_NULLABLE

            ,CASE

                  WHEN col.DATA_TYPE = 'bit' THEN col.DATA_TYPE

                  WHEN col.DATA_TYPE = 'datetime' THEN col.DATA_TYPE

                  WHEN col.DATA_TYPE = 'float' THEN col.DATA_TYPE

                  WHEN col.DATA_TYPE = 'int' THEN col.DATA_TYPE

                  WHEN col.DATA_TYPE = 'numeric' THEN col.DATA_TYPE

                  WHEN col.DATA_TYPE = 'smallint' THEN col.DATA_TYPE

                  WHEN col.DATA_TYPE = 'varbinary' THEN col.DATA_TYPE

                  WHEN col.DATA_TYPE = 'varchar' THEN col.DATA_TYPE + '('+ convert(varchar(50),col.CHARACTER_MAXIMUM_LENGTH) +')'

                  WHEN col.DATA_TYPE = 'nvarchar' THEN col.DATA_TYPE + '('+ convert(varchar(50),col.CHARACTER_MAXIMUM_LENGTH) +')'

            END as DATA_TYPE

            ,col.CHARACTER_SET_NAME

            ,col.COLLATION_NAME

            ,cast(des.value AS VARCHAR(400)) AS col_desc

      FROM

            information_schema.columns col

                  LEFT OUTER JOIN ::fn_listextendedproperty(NULL, 'user','dbo','table',@tableName,'column', default) des

                        ON col.column_name = des.objname COLLATE latin1_general_ci_ai

      WHERE col.table_name = @tablename

      SET @i = @i +1

END

-- 2.0: Output list of tables and views.

-- SELECT * FROM @eProp

-- 3.0: Output list of tables and views with assoicated columns and descriptive text for each column.

SELECT

            sch.name AS 'Schema Name'

            ,so.name AS 'Object Name'

            ,so.type_desc AS 'Object Type'

            ,soCol.column_id as 'Field Order'

            ,soCol.name AS 'Field Name'

            ,ep.col_desc AS 'Field Description'

            ,DEP.referenced_database_name AS 'Referenced Database'

            ,rso.type_desc AS 'Referencing Object Type'

            ,dep.referencing_id AS 'Referencing Object ID'

                  ,rso.name AS  'Referencing Object Name'

                  ,dep.referenced_id AS 'Referenced Object ID'

            ,rsoCol.name AS 'Referenced Object Name'

            ,dep.referencing_minor_id

            ,dep.referencing_class_desc AS ReferenceClass

            ,dep.is_schema_bound_reference AS IsSchemaBound

           

FROM

            sys.objects so

            INNER JOIN sys.schemas AS sch ON so.schema_id = sch.schema_id

            LEFT JOIN sys.columns AS soCol

                        ON so.object_id = socol.object_id

            FULL OUTER JOIN @eProp ep

                        ON so.name = ep.TABLE_NAME

                        AND soCol.name = ep.COLUMN_NAME

                        AND soCol.column_id = ep.ORDINAL_POSITION

            LEFT JOIN sys.sql_expression_dependencies dep

                        ON dep.referencing_id = so.object_id

            LEFT JOIN sys.objects AS rso

         ON dep.referenced_id = rso.object_id

    LEFT JOIN sys.columns AS rsoCol

                        ON dep.referenced_minor_id = rsoCol.column_id

                        AND dep.referenced_id = rsoCol.object_id

--                      AND DEP.referenced_class IN (0, 1)

-- WHERE

--      so.[type] IN ('U','V','F')    

ORDER BY

            sch.schema_id

            ,so.[type]

            ,so.name

            ,socol.Column_ID