SQL Server – Transact SQL – Table – Rules

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

listRules

 

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

listRulesAndColumnDepedency

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

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s