January 15, 2013 at 12:53 am
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?
January 15, 2013 at 1:40 am
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'
January 17, 2013 at 7:43 am
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??
January 17, 2013 at 7:54 am
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
January 17, 2013 at 8:38 am
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
January 17, 2013 at 8:39 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply