mssqltips logo

Find all SQL Server columns of a specific data type using Policy Based Management

By:   |   Updated: 2010-04-28   |   Comments (4)   |   Related: More > Policy Based Management

Problem

SQL Server 2008 introduced Policy-Based Management (PBM), a way to centralize checking the compliance of policies that you can dictate. Unfortunately the facets and conditions that ship out of the box will not always cover the scenario(s) you are interested in evaluating.  Check out this tip to learn how to extend the Policy Based Management.

Solution

Using the ExecuteSql() function, you can extend PBM's functionality to evaluate anything that you can check with T-SQL. As an example, at one point I was interested in knowing how many tables across my systems had DATETIME columns, as I was starting to evaluate converting them to less granularity (DATE) and more granularity (DATETIME2) - but pointing and clicking through PBM's facets did not yield an obvious answer. While there are other ways to find columns of a specific type, they would involve running the same query against multiple databases and multiple servers manually. I was interested in finding a way to do this with PBM, so that I could run it across my systems on-demand, as I made progress on my conversion project.

First it is important to note that there are four main concepts in Policy-Based Management:

Concept

Description

Target This is the type of entity you are checking for compliance.
Example: Table.
Facet This is the set of properties that a target exposes to conditions.
Example: A table has properties such as Name and HasClusteredIndex.
Condition This is the compliance question you are asking.
Example: "Does every table in this database have a clustered index?"
Policy This is the container that allows you to check (and even enforce) condition compliance.

If you look through the Facets that currently ship with SQL Server 2008 and SQL Server 2008 R2, you'll see there is nothing that helps describe or define the properties of columns within a table. There are probably several other conditions you might want to check that would be in the same boat - no pre-defined facets to query directly via Policy Management.

(To see the Facets available, open Object Explorer and expand Management > Policy Management > Facets.)

look through the Facets that currently ship with SQL Server 2008 and SQL Server 2008 R2

You are going to end up using a Table facet, even if the facet doesn't directly support what you are after. The reason is that you'll want to check each table, and using the Table facet essentially ensures that the policy will evaluate every user table.

Using standard T-SQL, in order to find any tables with DATETIME columns, you could run a query against the sys.columns catalog view, checking for rows where system_type_id = 61 (you can find the system_type_id by checking the catalog view sys.types). For example, the following query will return a row for each table that has at least one DATETIME column:

SELECT DISTINCT
 QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
     + '.' + QUOTENAME(OBJECT_NAME([object_id]))
 FROM sys.columns
 WHERE [system_type_id] = 61;

As mentioned before, in order to check for these columns at any time, you would have to save this query and run it multiple times against multiple servers. Instead, you can create a condition, wrapping a similar query in the ExecuteSql() function, and then use Policy-Based Management to check for these columns with a couple of clicks.

Converting this to a query that can be evaluated as a PBM condition only requires a few changes. For one, the query must return a scalar value, such as a count or a string. The query must also be able to identify each target as the evaluation engine uses it. Thankfully, the evaluation process allows you to place variables for substitution, so you can get context for the current target (such as object name) at runtime. The two you will use in this case are @@SchemaName and @@ObjectName (surprisingly, @@ObjectId is not a possible variable to use here). Note that these are not T-SQL variables (like @@SERVERNAME) but rather special parameters that are replaced as the query is evaluated for each target. So the query that will end up being used in the condition is as follows:

ExecuteSql('Numeric', 'SELECT COUNT(*) 
    FROM sys.columns 
    WHERE [system_type_id] = 61 
    AND [object_id] = OBJECT_ID(@@SchemaName + ''.'' + @@ObjectName);')

In order to create this condition, you need to right-click the Conditions node under Policy Management, and select "New Condition...":

click the Conditions node under Policy Management

Since policies and conditions are supposed to be expressed in the positive, you should call this condition "Table has no DATETIME columns." Pick the Facet "Table", then under Expression, click on the "Click here to add a clause" area. To get the advanced condition editor, click on the first [...] button:

call this condition "Table has no DATETIME columns." Pick the Facet "Table"

This will bring up the Advanced Edit dialog, where you will paste the query from above into the "Cell value:" area:

 paste the query from above into the "Cell value:" area

Click OK, then in the "Value" field, enter 0:

then in the "Value" field, enter 0

Of course you can do all of this without the user interface, which is the approach I advocate in a lot of cases - but not this one. There is a stored procedure called sp_syspolicy_add_condition that you can use instead. However, to see why I recommend using the UI instead, right-click the condition you just created, and choose Script Condition As > CREATE To > New Query Editor Window. I won't spoil the surprise here, but I think you'll agree that the UI is a better option in this case.

Moving on, you now need to create a policy that will allow you to evaluate the condition you've created. So right-click the Policies node under Policy Management, and select "New Policy...":

right-click the Policies node under Policy Management, and select "New Policy..."

On the resulting dialog, name the Policy (you can be more creative, but I used "DATETIME column policy"), and choose the condition you created earlier. You can create additional conditions to target the policy at only user databases, or even only at a single database, but for now you can leave those as Every Table in Every Database. Since you are running this on demand, you can leave the Evaluation Mode at the default setting, and click OK:

you can leave the Evaluation Mode at the default setting, and click OK

Now to evaluate the policy (and make sure everything is set up correctly), simply right-click the policy and choose "Evaluate":

simply right-click the policy and choose "Evaluate"

You will end up with the following dialog (including the horribly-impossible-to-read policy name), and a warning:

You will end up with the following dialog

This is just reminding you that the ExecuteSql() call is running a script that is considered unsafe, and that you should be careful about what scripts you allow into these conditions. You will get this warning every time you evaluate a policy with a condition that uses ExecuteSql(). Assuming you are still dealing with the simple condition created above, click the "Evaluate" button, then click the "Run" button on the second warning that pops up (here you can click "Do not show this message again" if you like). You will end up with the following dialog, and if you have any tables with DATETIME columns, they will show up as violating the policy:

 reminding you that the ExecuteSql() call is running a script that is considered unsafe

Scrolling over to the right, you will see a Details column with a "View" hyperlink that is, again, barely legible if that row is selected. Clicking on the "View" link will show you the following details panel which, while lacking context, tells you how many DATETIME columns were found in the actual table:

Clicking on the "View" link will show you the following details panel which, while lacking context, tells you how many DATETIME columns were found in the actual table

I intentionally chose DATETIME for its simplicity. In fact you may want to write more complex conditions that will find columns with more granular characteristics, such as NUMERIC(4,0), or even conditions that will find tables that have more than one MAX type. The sky is the limit here; as demonstrated, if you can evaluate something based on a scalar value returned from a T-SQL query, you can build a condition and policy around it - making this type of compliance check much more automatic and easily portable to other systems.

Next Steps


Last Updated: 2010-04-28


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, June 02, 2014 - 1:50:03 PM - Aaron Bertrand Back To Top

@Swoozie the policy runs in the background - there is no way to interact with it directly. Exactly what do you plan on passing into this variable? From where? Perhaps you should ask a specific question on the site?

http://sqlserverquestions.mssqltips.com/questionlist.asp


Monday, June 02, 2014 - 10:48:47 AM - Swoozie Back To Top

I must have done something wrong somewhere that I didnt catch when I was recreating, because I am getting an error that they are undeclared.

however, on that note, can variables be used in conditions?


Monday, June 02, 2014 - 10:21:27 AM - Aaron Bertrand Back To Top

@Swoozie, @@ObjectName and @@SchemaName are built-in functions and they are populated automatically as the policy executes. There is nothing to declare or pass.


Monday, June 02, 2014 - 10:04:36 AM - Swoozie Back To Top

The condition contains a variable.  The instructions do not specify how to declare the variable nor how to pass a parameter to it.  Please Clarify.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools