how to find dependent objects on Default or Rule in sql 2008

  • I am able to see the list of columns or table which is bound to my rule or default using sp_depends.what are other ways to find it?

  • Query sys.default_constraints, link it back to sys.columns and you get the default name, table name and column name.

    For rules, they are a depereciated feature, you should consider changing them all to check constraints.

    SELECT

    OBJECT_NAME(sc.rule_object_id) AS RuleName

    ,OBJECT_NAME(sc.object_id) AS TableName

    ,sc.name AS ColumnName

    FROM

    sys.objects so

    JOIN

    sys.columns sc

    ON

    so.object_id = sc.rule_object_id

    WHERE

    so.type_desc = 'rule'

  • Thanks for your answer but My question has a problem.It should have been that I am not able to see dependent object of rule through sp_depends,I tried all ways given in books online??

  • Dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects.

    http://msdn.microsoft.com/en-us/library/ms345449%28v=sql.105%29.aspx

  • this gets me a lot of information about any table/columns that have a rule:

    SELECT OBJECT_NAME(OBJS.object_id) As TableName,* FROM sys.columns COLS

    INNER JOIN sys.objects OBJS

    ON OBJS.[object_id] = COLS.[object_id]

    INNER JOIN sys.sql_modules MODS

    ON COLS.[rule_object_id] = MODS.[object_id]

    WHERE COLS.[rule_object_id] <> 0

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • and some ready made code to test it: creates a rule and a column that uses it:

    CREATE RULE [dbo].[NumericCharsOnly]

    AS

    --@value NOT LIKE '%[0-9,-+]%' ESCAPE '!' --bad way...minus and spec chars need to be first!

    @value NOT LIKE '%[^-+,0-9]%' ESCAPE '!'

    GO

    --create a "type" , and bind the rule to teh type

    CREATE TYPE [dbo].[numchar] FROM [varchar](20) NULL

    GO

    EXEC sys.sp_bindrule @rulename=N'[dbo].[NumericCharsOnly]', @objname=N'[dbo].[numchar]' , @futureonly='futureonly'

    GO

    --a simple test table.drop table example

    create table example(exampleid int identity,test numchar)

    --insert some test data.

    insert into example(test) values ('0000')

    GO

    insert into example(test) values ('00a00') --fails! all is good

    GO

    insert into example(test) values ('0000&444') --fails as expected

    GO

    insert into example(test) values ('-0000') --failed when i did not want it too

    GO

    insert into example(test) values ('+0000') --failed when i did not want it too

    GO

    drop table example

    drop type [numchar]

    drop rule [NumericCharsOnly]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply