Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
sej2008
sej2008
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 394
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?
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6108 Visits: 6079
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'





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


sej2008
sej2008
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 394
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??
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6108 Visits: 6079
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



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39012
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!

Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39012
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search