Wednesday, October 17, 2007

User-defined data type dependencies

MSSQLServer has got sp_depends system sp to display the dependencies for a stored procedure, table and view. sp_depends does not support to display the User-defined data type dependencies. For custom database, we will create user defined data types (UDDT) and add this UDDT to a column/variable in a table/stored procedure. This sp (UDTDepends) will help to display the User-defined data type dependencies in a database for a Tables and Stored Procedure.

Compile this sp in a database and execute the same. It will display the dependencies for the user defined data types if it exists in a database.

Criticize are welcome.

if exists (select * from sysobjects where id = object_id(N'[dbo].[UDTDepends]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UDTDepends]

Create Proc UDTDepends


SELECT As "ObjectName", As "ColumnName", type_name(st.xusertype) As "UserDefinedDataType",
type_name(sc.xtype) As "DataType", st.length As "DataLength"

FROM sysobjects so, syscolumns sc, systypes st

WHERE so.xtype In ('U', 'P') and = and sc.xusertype = st.xusertype and
sc.usertype Not In (0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 19, 20, 21, 22, 23, 24, 80)

ORDER BY so.xtype desc,,


No comments: