Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to find dependent objects on Default or Rule in sql 2008 Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 12:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 2:43 AM
Points: 135, Visits: 265
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?
Post #1407059
Posted Tuesday, January 15, 2013 1:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 5,221, Visits: 5,119
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
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

Post #1407079
Posted Thursday, January 17, 2013 7:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 2:43 AM
Points: 135, Visits: 265
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??
Post #1408425
Posted Thursday, January 17, 2013 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 5,221, Visits: 5,119
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
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

Post #1408430
Posted Thursday, January 17, 2013 8:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 12,923, Visits: 32,313
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1408467
Posted Thursday, January 17, 2013 8:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 12,923, Visits: 32,313
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1408468
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse