What is a Rule
MSFT
A rule specifies the acceptable values that can be inserted into a column ( Link)
List Rules
SQL Code
SELECT [Schema] = SCHEMA_NAME(tblSO.schema_id) , [ObjectName] = tblSO.name , [Definition] = COALESCE ( tblSSM.[definition] , tblSSQM.[definition] ) FROM sys.objects AS tblSO LEFT OUTER JOIN sys.sql_modules AS tblSSM ON tblSO.[object_id] = tblSSM.[object_id] LEFT OUTER JOIN sys.system_sql_modules AS tblSSQM ON tblSO.object_id = tblSSQM.[object_id] WHERE ( ( tblSO.[type] =N'R' ) and ( ( tblSO.parent_object_id = 0) or ( tblSO.parent_object_id is null) ) )
Output
List Rules & Column Bindings
SQL Code
SELECT [ruleName] = quoteName(OBJECT_SCHEMA_NAME(tblSMR.object_id)) + '.' + quoteName(Object_Name(tblSMR.object_id)) , [rule] = tblSMR.[definition] , [QuotedIdentifier] = case (tblSMR.[uses_quoted_identifier]) when 0 then 'No' else 'Yes' end , [AnsiNulls] = case (tblSMR.[uses_ansi_nulls]) when 0 then 'No' else 'Yes' end , [Table] = quoteName(OBJECT_SCHEMA_NAME(tblSO.object_id)) + '.' + quoteName(OBJECT_NAME(tblSO.object_id)) , [columnName] = tblSC.[name] FROM sys.objects tblSO INNER JOIN sys.columns tblSC ON tblSO.[object_id] = tblSC.[object_id] INNER JOIN sys.sql_modules tblSMR ON tblSC.[rule_object_id] = tblSMR.[object_id]
Output
References
Rule
- Create Rule
Link
Metadata – References
- sys.all_objects (Transact-SQL)
Link
Metadata – Q&A
- How to find dependent objects on Default or Rule in sql 2008
Link