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

By: Aaron Bertrand   |   Updated: 2010-04-28   |   Comments (4)   |   Related: > 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:

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.)

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...":

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:

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

Click OK, 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...":

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:

Now to evaluate the policy (and make sure everything is set up correctly), 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:

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:

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:

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

• Create a condition that uses a table Facet and, using ExecuteSql(), checks sys.columns for a specific data type.
• Create an on-demand policy that uses the condition.
• Evaluate the policy.
• Review the following tips, blog posts and Books Online topics:
  • Using Policy-Based Management in SQL Server 2008
  • Monitor Your SQL Server Virtual Log Files with Policy Based Management
  • Validate SQL Server Backups Exist with Policy Based Management
  • Evaluating and Implementing Policies in Multiple Instances in SQL Server 2008
  • SQL Server Policy-Based Management Blog : ExecuteSQL()
  • Administering Servers by Using Policy-Based Management
  • Tutorial: Evaluating Best Practices by Using Policy-Based Management
  • Using Date and Time Data

About the author

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 also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips