PaulSpoerry.com

Social Media, technology, and geeky stuff for your brain.

  • Home
  • About
    • Privacy Policy
  • Categories
    • Google+ Posts
    • Site news
    • Tech
      • Android
      • Apple
      • Chrome
      • Gadgets
      • Hacking
      • Linux
      • OSX
      • Privacy
      • Web Life
        • Bittorrent
        • Facebook
        • FireFox
        • GMail
        • Google
        • Google+
        • Twitter
        • WordPress
        • Windows
          • Windows 7
    • Google+: Getting Started Guides
    • Games
    • Meditation
    • Politics
    • Science
    • That’s freakin hilarious
  • Code
    • FreeImageZoom
    • Post Editor for Google+™
    • The Plus Editor
  • Contact
You are here: Home / Tech / How to check if a SQL Server RULE is bound to a column

How to check if a SQL Server RULE is bound to a column

July 13, 2012 by Paul Spoerry Leave a Comment

A SQL Server RULE specifies the acceptable values that can be inserted into a database column. Let’s see how to find out if a RULE is bound to a column in SQL Server and how to remove them. A RULE is very much like a check constraint; except that they only work in T-SQL and Microsoft has deprecated them. A SQL Server RULE is intended to check information as new rows are inserted or when existing rows are updated. If the RULE is broken because the data is invalid the update fails. According to MSDN, if you’re thinking of using a SQL Server RULE… don’t. It’s deprecated.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use check constraints instead. Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE.

How to check if a SQL Server RULE is bound to a column

How to check if a SQL Server RULE is bound to a column

If you’re still reading it’s because you already have at least one SQL Server RULE in place. I’m sure you’ve seen tons of articles on how to create a SQL Server RULE, how to bind and unbind a SQL RULE rule on columns, and how to drop one. What I had trouble finding was information on how to check to see if a RULE is bound to a column or not.
SELECT so.name AS RuleNameI ran across this issue while tweaking an upgrade script. I typically use IF EXISTS very liberally before performing any kind of action in SQL. The code I was working on was attempting to unbind a SQL Server RULE from several tables, drop the RULE, then recreate the SQL Server RULE with the new values and rebind it to the tables. However, the rule was missing from one of the tables and this causing an error. You can run the statement below to query your data. Simply comment/uncomment the lines in the WHERE clause to search by table name, column name, or SQL Server RULE name.

	,OBJECT_NAME(sc.object_id) AS TableName
	,so.type_desc
	,sc.name AS ColumnName
FROM sys.objects so
JOIN sys.columns sc
	ON so.object_id = sc.rule_object_id
WHERE 
--sc.name = 'myCol' --search by column name
--AND OBJECT_NAME(sc.object_id) = 'myTable' --search by table name
so.name = 'chkRule' --search by rulename

Now that you have an idea for the data you’re looking at, using an EXISTS statement before trying to use sp_unbindRule is easy.

IF EXISTS (SELECT so.name 
	FROM sys.objects so
	JOIN sys.columns sc
		ON so.object_id = sc.rule_object_id
	WHERE 
		OBJECT_NAME(sc.object_id) = 'myTable' -- table name
		AND sc.name = 'myColumn' --column name)
BEGIN
	EXEC sp_unbindrule 'myTable.myColumn'
END

 

 

Filed Under: Tech Tagged With: a, bound, MSSQL, table, with

About Paul Spoerry

I’m a groovy cat who’s into technology, Eastern Thought, and house music. I’m a proud and dedicated father to the coolest little guy on the planet (seriously, I'm NOT biased). I’m fascinated by ninjas, the Internet, and anybody who can balance objects on their nose for long periods of time.

I have a utility belt full of programming languages and a database of all my knowledge on databases... I practice code fu. Oh, I've also done actual Kung Fu, and have a black belt in Tae Kwon Do.

I run. I meditate. I dance. I blog at PaulSpoerry.com, tweet @PaulSpoerry, and I'm here on Google+.

I'm currently work for IBM developing web enabled insurance applications for IBM and support and develop a non-profit called The LittleBigFund.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Copyright © 2023 · Epik on Genesis Framework · WordPress · Log in