August 19, 2015 at 10:33 am
I have been asked to create a report for one of our clients. The report is pretty basic but I am concerned about the overheads with my planned approach.
The report is at a table and field grain to include values for:
* Min column value
* Max column value
* Number of discrete values
* Number of populated values (not NULL)
My current plan is to have a cursor over a limited view of sys.tables and sys.columns that will run a dynamic SQL query to import the results into a table that I can then output.
There must be a better way of doing this and I don't have access to any DQS services.
Thanks
August 19, 2015 at 10:54 am
ryanjb1989 (8/19/2015)
I have been asked to create a report for one of our clients. The report is pretty basic but I am concerned about the overheads with my planned approach.The report is at a table and field grain to include values for:
* Min column value
* Max column value
* Number of discrete values
* Number of populated values (not NULL)
My current plan is to have a cursor over a limited view of sys.tables and sys.columns that will run a dynamic SQL query to import the results into a table that I can then output.
There must be a better way of doing this and I don't have access to any DQS services.
Thanks
Your method is fine and is probably how I would do it too for a quick-and-dirty report. Just make sure you hit each table ONCE by dynamically scripting all necessary column aggregates for each table with your metadata cursor. You should also probably NOLOCK every table access to prevent this action from blocking real activity and also consider OPTION (MAXDOP ?) to keep it from crushing the box. I would use 1 or maybe 2 there depending on the power of the server (IO especially in this case).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply