March 30, 2009 at 5:47 am
All-
I'm trying to write a script that will do some basic data profiling and put the output into another data profiling database. I want the code to loop through every table, then through every column in the chosen database, as well as grab some info from the system tables. As of now, I am using nested cursors to accompllish this, but am not sure this is the best way to go. I have the outer cursor put data such as the table name, column name, data type, data precision, data scale, and nullable into variables, then use the inner cursor to query each actual table column for things like min, max, and average data length, density, number of nulls and that sort of thing. I was trying to use the table name variable and column name variable from the outer cursor to make the inner cursor query easier. For example - assume that the table name variable and column name variable come from the outer cursor that queries the system tables - I want to do-
SELECT
count(*) as NumRows
sum(case when @ColumnName is null then 1 else 0 end) as NumNulls
count(distinct @ColumnName) as NumDistinctValues
FROM
@TableName
I just used a couple of examples of what I want in the select statement, but I want to be able to execute that select statement while looping through every column of every table. The problem I run into is that @TableName is just a varchar of the table name, not a table variable.
Any better ways to accomplish what I'm trying to do, or does anyone know how to get around that?
March 30, 2009 at 7:05 am
To do this in TSQL, you would need to use ad hoc queries and build your query as a string. SP_EXECUTESQL would be the best use.
Generally, something like this might be better managed through a coding language such as powershell or c# instead of scripting it through TSQL.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 30, 2009 at 8:24 am
That's what I was thinking but was trying to see if there was a way in TSQL that I just didn't know about. I'll look into the SP_EXECUTESQL though....thanks for the response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply