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

1. Create Rule
   Link

Metadata – References

1. sys.all_objects (Transact-SQL)
   Link

Metadata – Q&A

1. How to find dependent objects on Default or Rule in sql 2008
   Link