May 18, 2012 at 9:09 am
Gazareth (5/18/2012)
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
Ouch... Where would I put the Table & Column Name to include in the output?
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 9:10 am
Lynn Pettis (5/18/2012)
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?
Msg 1033, Level 15, State 1, Line 23
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
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 9:13 am
Welsh Corgi (5/18/2012)
Lynn Pettis (5/18/2012)
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?
Msg 1033, Level 15, State 1, Line 23
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Okay, what does this mean? Is the code only a part of something bigger? The second query doesn't appear to be in a view, inline function, derived table, subquery, or common table expression.
May 18, 2012 at 9:25 am
Lynn,
Don't worry about it.
I'm not using it.
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 9:50 am
Welsh Corgi (5/18/2012)
Lynn,Don't worry about it.
I'm not using it.
Okay. I was noticing some things about the original query. The first query with the LEFT JOINS; the joins between sys.schemas, sys.tables, and sys.partitions are basically inner joins due to the where clause: p.rows is not null.
Also, from what I could tell, these two queries could actually be combined into one query, just need to know (but obviously not important now) the purposre of the query.
I'll stop looking into it.
Thanks
May 18, 2012 at 10:02 am
Lynn Pettis (5/18/2012)
Welsh Corgi (5/18/2012)
Lynn,Don't worry about it.
I'm not using it.
Okay. I was noticing some things about the original query. The first query with the LEFT JOINS; the joins between sys.schemas, sys.tables, and sys.partitions are basically inner joins due to the where clause: p.rows is not null.
Also, from what I could tell, these two queries could actually be combined into one query, just need to know (but obviously not important now) the purposre of the query.
I'll stop looking into it.
Thanks
The queries have already been combined. 🙂
I'm not using the two statements that you just refered to.
I previously posted the following.
;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
One issue is that I need to Table Name & Column name to be listed in the output.
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 10:32 am
What is your expected output?
May 18, 2012 at 10:56 am
You are really doing a lot of extra work that you don't need to be doing.
SELECT
s.name AS schemaName,
t.name AS TableName,
c.name AS ColumnName,
rcnt.rowcnt,
'select case when count(distinct ' + c.name +
') > 0 then 1 else 0 end as Populated, count(distinct ' + c.name +
') as Cardinality from ' + s.name + '.' + t.name
FROM
sys.schemas s
INNER JOIN sys.tables t
ON (s.schema_id = t.schema_id)
INNER JOIN sys.columns c
ON (t.object_id = c.object_id)
-- INNER JOIN sys.objects o -- not needed as sys.tables only has user defined tables
-- ON (t.object_id = o.object_id
-- AND o.type = 'U')
CROSS APPLY (SELECT SUM(p.rows) FROM sys.partitions p WHERE t.object_id = p.object_id AND p.index_id IN (0,1)) rcnt(rowcnt)
WHERE
rcnt.rowcnt > 0;
May 18, 2012 at 11:04 am
Be sure to check out the change I made to the code I posted above. The join to sys.objects isn't even needed.
May 18, 2012 at 11:12 am
What I need in the output is the Table Name, Column name, the Distinct Values & a record count.
TableName Column Name STATUS_OPEN RecordCount
WPA_DIARY_ENTRY STATUS_OPEN -1 215
WPA_DIARY_ENTRY STATUS_OPEN 0 5071
Thank you.
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 11:13 am
Lynn Pettis (5/18/2012)
Be sure to check out the change I made to the code I posted above. The join to sys.objects isn't even needed.
Nice code.
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 11:21 am
Welsh Corgi (5/18/2012)
What I need in the output is the Table Name, Column name, the Distinct Values & a record count.TableName Column Name STATUS_OPEN RecordCount
WPA_DIARY_ENTRY STATUS_OPEN -1 215
WPA_DIARY_ENTRY STATUS_OPEN 0 5071
Thank you.
This doesn't match what you have in your original code. Care to give us the full description of what you are trying to accomplish and what you need instead of itty bitty pieces that aren't adding up? I feel like I am spinning my wheels for nothing.
May 18, 2012 at 11:40 am
I will be able to use your script for other situations.
Thanks for pointing out the issues with the code.
I liked your use of CROSS APPLY, etc.
I'm sorry that I was not clear.
On my second post I stated that "I need a variation of the script to determine what columns are populated and the cardinality of each row."
I should have opened a new post and rather than using the word cardinality I should have stated Distinct Values.
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 11:45 am
Welsh Corgi (5/18/2012)
I will be able to use your script for other situations.Thanks for pointing out the issues with the code.
I liked your use of CROSS APPLY, etc.
I'm sorry that I was not clear.
On my second post I stated that "I need a variation of the script to determine what columns are populated and the cardinality of each row."
I should have opened a new post and rather than using the word cardinality I should have stated Distinct Values.
Now, don't get upset, but look with eye in your signature block. See that first article you reference, you really should follow the advice when posting. Show us what you want and are expecting from the code we write. I personally work better when I see what needs to be returned rather than just a description. Both are great, but seeing helps with understanding.
May 18, 2012 at 11:55 am
Welsh Corgi (5/18/2012)
I will be able to use your script for other situations.Thanks for pointing out the issues with the code.
I liked your use of CROSS APPLY, etc.
I'm sorry that I was not clear.
On my second post I stated that "I need a variation of the script to determine what columns are populated and the cardinality of each row."
I should have opened a new post and rather than using the word cardinality I should have stated Distinct Values.
Also, this doesn't really match up with what you posted either, as it shows the column twice.
As I think about this, I am reminded of the TMR database I ported and modified from some Oracle code. It captured information about databases, tables, columns, values in columns, and maybe a few other things that I can't remember (it was about 7 to 8 years ago when I was doing this).
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply