May 18, 2012 at 6:52 am
I wrote some Stored Procedures using CTE but not that much.
How could I convert the code below to CTE?
SELECT s.Name AS SchemaName ,
t.Name AS TableName ,
SUM(p.rows) AS RecordCount
INTO #Temp
FROM sys.schemas AS s
LEFT JOIN sys.tables AS t ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions AS p ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
WHERE p.rows IS NOT NULL
GROUP BY s.Name , t.Name
HAVING SUM(p.rows) > 0
ORDER BY s.Name, t.Name;
SELECT DISTINCT obj.Name AS TableName-- , col.Name AS ColumnName
FROM sys.Objects AS obj
INNER JOIN sys.columns AS Col ON obj.object_id = col.object_id
INNER JOIN #Temp AS tmp ON tmp.TableName = obj.Name
WHERE obj.type = 'U'
ORDER BY obj.Name, col.Name;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2012 at 7:01 am
;WITH cte_firstset AS (
SELECT s.Name AS SchemaName ,
t.Name AS TableName ,
SUM(p.rows) AS RecordCount
FROM sys.schemas AS s
LEFT JOIN sys.tables AS t ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions AS p ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
WHERE p.rows IS NOT NULL
GROUP BY s.Name , t.Name
HAVING SUM(p.rows) > 0
)
SELECT DISTINCT obj.Name AS TableName, col.Name AS ColumnName
FROM sys.Objects AS obj
INNER JOIN sys.columns AS Col ON obj.object_id = col.object_id
INNER JOIN cte_firstset AS tmp ON tmp.TableName = obj.Name
WHERE obj.type = 'U'
ORDER BY obj.Name, col.Name;
May 18, 2012 at 7:02 am
with X (schemaname,tablename,recordcount)
as
(
SELECT s.Name AS SchemaName ,
t.Name AS TableName ,
SUM(p.rows) AS RecordCount
FROM sys.schemas AS s
LEFT JOIN sys.tables AS t ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions AS p ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
WHERE p.rows IS NOT NULL
GROUP BY s.Name , t.Name
HAVING SUM(p.rows) > 0
)
SELECT DISTINCT obj.Name AS TableName-- , col.Name AS ColumnName
FROM sys.Objects AS obj
INNER JOIN sys.columns AS Col ON obj.object_id = col.object_id
INNER JOIN X AS tmp ON tmp.TableName = obj.Name
WHERE obj.type = 'U'
ORDER BY obj.Name;
MVDBA
May 18, 2012 at 7:13 am
Thank you for all of your help.
I need a variation of the script to determine what columns are populated and the cardinality of each row.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2012 at 7:18 am
I don't think you can, directly from that query.
You'd have to generate some dynamic sql based on your results from the query you have.
May 18, 2012 at 7:21 am
Gazareth (5/18/2012)
I don't think you can, directly from that query.You'd have to generate some dynamic sql based on your results from the query you have.
And why would you need to do that?
May 18, 2012 at 7:30 am
It is for a Data Warehouse Project.
If the column in the source system is not populated then we don't have to analyze it.
One reason we want to know the cardinality is so that we can determine if it should be indexed.
Thanks again for all of the help.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2012 at 7:31 am
Gazareth (5/18/2012)
I don't think you can, directly from that query.You'd have to generate some dynamic sql based on your results from the query you have.
If I could just get it to generate the SELECT Statements then I could save it to a file.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2012 at 7:47 am
mtassin (5/18/2012)
Gazareth (5/18/2012)
I don't think you can, directly from that query.You'd have to generate some dynamic sql based on your results from the query you have.
And why would you need to do that?
I'm happy to hear of other ways to do it 🙂
Welsh Corgi - that will work too, something like (untested):
SELECT 'SELECT CASE WHEN COUNT(DISTINCT ' + ColumnName + ') > 0 THEN 1 ELSE 0 END AS Populated,
COUNT(DISTINCT ' + ColumnName + ') AS Cardinality
FROM ' + TableName
FROM
(
SELECT DISTINCT obj.Name AS TableName, col.Name AS ColumnName
FROM sys.Objects AS obj
INNER JOIN sys.columns AS Col ON obj.object_id = col.object_id
INNER JOIN cte_firstset AS tmp ON tmp.TableName = obj.Name
WHERE obj.type = 'U'
--ORDER BY obj.Name, col.Name;
) tbl
In place of your select should generate the checks you're after.
Cheers
Edit: If there's tables in different schemas, you'll need to put the schema name into the generated selects too.
May 18, 2012 at 8:15 am
Gazareth.
Thanks for the help!
Unfortunately I get an error for the cte is being referenced.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'cte_firstset'.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2012 at 8:22 am
No problem. Yep - sorry you'll need to run the CTE part of your code too.
My bit replaces the select you already had after the CTE definition.
May 18, 2012 at 8:34 am
Thanks, it generated the SELECT Statements but I need to return the Table Name and Column Name in the output.
I'm not sure to handle text columns?
If the column is text do a DISTINCT COUNT and CAST The Column AS VARCHAR?
Msg 8117, Level 16, State 1, Line 264
Operand data type text is invalid for count operator.
Msg 8117, Level 16, State 1, Line 329
Operand data type text is invalid for count operator.
Msg 8117, Level 16, State 1, Line 369
Operand data type text is invalid for count operator.
;WITH cte_firstset AS (
SELECT s.Name AS SchemaName ,
t.Name AS TableName ,
SUM(p.rows) AS RecordCount
FROM sys.schemas AS s
LEFT JOIN sys.tables AS t ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions AS p ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
WHERE p.rows IS NOT NULL
GROUP BY s.Name , t.Name
HAVING SUM(p.rows) > 0
)
SELECT 'SELECT CASE WHEN COUNT(DISTINCT ' + ColumnName + ') > 0 THEN 1 ELSE 0 END AS Populated,
COUNT(DISTINCT ' + ColumnName + ') AS Cardinality
FROM ' + TableName
FROM
(
SELECT DISTINCT obj.Name AS TableName, col.Name AS ColumnName
FROM sys.Objects AS obj
INNER JOIN sys.columns AS Col ON obj.object_id = col.object_id
INNER JOIN cte_firstset AS tmp ON tmp.TableName = obj.Name
WHERE obj.type = 'U'
--ORDER BY obj.Name, col.Name;
) tbl
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2012 at 8:42 am
well text is heavily deprecated, but... you could convert text to varchar(max)... that seems to work for me...
May 18, 2012 at 8:50 am
Something like:
SELECT 'SELECT ' + TableName + ' AS TableName, ' + ColumnName + ' AS ColumnName, CASE WHEN COUNT(DISTINCT ' + ...
Yes, you'll need to do something with the text fields. CAST should work.
If you only want to CAST the text fields then you'll need to identify them & work another CASE statement into the generated code.
Cheers
Gaz
May 18, 2012 at 8:55 am
Welsh Corgi (5/18/2012)
I wrote some Stored Procedures using CTE but not that much.How could I convert the code below to CTE?
SELECT s.Name AS SchemaName ,
t.Name AS TableName ,
SUM(p.rows) AS RecordCount
INTO #Temp
FROM sys.schemas AS s
LEFT JOIN sys.tables AS t ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions AS p ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
WHERE p.rows IS NOT NULL
GROUP BY s.Name , t.Name
HAVING SUM(p.rows) > 0
ORDER BY s.Name, t.Name;
SELECT DISTINCT obj.Name AS TableName-- , col.Name AS ColumnName
FROM sys.Objects AS obj
INNER JOIN sys.columns AS Col ON obj.object_id = col.object_id
INNER JOIN #Temp AS tmp ON tmp.TableName = obj.Name
WHERE obj.type = 'U'
ORDER BY obj.Name, col.Name;
Is there a reason you have the col.Name commented out above?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply