October 3, 2017 at 11:24 am
Hello Experts
I want to look at the various characteristics of the tables in which a column with a specified name occurs.
For some things this is easily done with system views; e.g:SELECT C.[object_id]
,C.name
,[column_id]
,[system_type_id]
,[user_type_id]
,[max_length]
,[precision]
,[scale]
,T.name
,T.max_column_id_used
FROM MyDB.[sys].[columns] as C
inner join MyDB.sys.tables as T
on (C.object_id=T.object_id)
and C.name like 'ConditionKey'
However I would also like to add a column showing the current number of rows in the table containg the field. It would be nice to add a line (just before the FROM) with something like the following, which doesn't work but hopefully conveys the idea:
,sp_executesql( N'select count(*) from '+ T.name) as num_rows
Is there a working approach along this line or do I have to resort to a scalar function?
Thanks in advance
MarkD
October 3, 2017 at 12:18 pm
Mark Dalley - Tuesday, October 3, 2017 11:24 AMHello ExpertsI want to look at the various characteristics of the tables in which a column with a specified name occurs.
For some things this is easily done with system views; e.g:SELECT C.[object_id]
,C.name
,[column_id]
,[system_type_id]
,[user_type_id]
,[max_length]
,[precision]
,[scale]
,T.name
,T.max_column_id_used
FROM MyDB.[sys].[columns] as C
inner join MyDB.sys.tables as T
on (C.object_id=T.object_id)
and C.name like 'ConditionKey'However I would also like to add a column showing the current number of rows in the table containg the field. It would be nice to add a line (just before the FROM) with something like the following, which doesn't work but hopefully conveys the idea:
,sp_executesql( N'select count(*) from '+ T.name) as num_rowsIs there a working approach along this line or do I have to resort to a scalar function?
Thanks in advance
MarkD
Maybe something like this?
SELECT C.[object_id]
,C.name
,[column_id]
,[system_type_id]
,[user_type_id]
,[max_length]
,[precision]
,[scale]
,T.name
,T.max_column_id_used
,r.row_count
FROM MyDB.[sys].[columns] as C
INNER JOIN MyDB.sys.tables as T on (C.object_id=T.object_id)
and C.name like 'ConditionKey'
CROSS APPLY( SELECT SUM(row_count) AS row_count
FROM MyDB.sys.dm_db_partition_stats s
WHERE s.object_id = t.object_id
AND index_id <= 1) r;
October 5, 2017 at 10:49 am
Thanks Luis
That seems to work perfectly, and after getting my head around CROSS APPLY and the partition_stats DMV somewhat, I am beginning to understand why. Just checking though: An index_id of 0 represents a heap table, an index_id of 1 represents a clustered index, which has the same number of rows as the table it indexes - is that right?
There is an ominous phrase in BOL about the row_count being "the approximate number of rows." What is the reason for this caveat and how can I ensure the count is accurate?
I am also wondering if the CROSS APPLY above could be recast as another inner join. I am trying to do this but have not yet succeeded. If I can understand the reasons it would definitely help my understanding.
I will come back to this tomorrow. Thanks again.
MarkD
October 5, 2017 at 11:20 am
Mark Dalley - Thursday, October 5, 2017 10:49 AMThanks LuisThat seems to work perfectly, and after getting my head around CROSS APPLY and the partition_stats DMV somewhat, I am beginning to understand why. Just checking though: An index_id of 0 represents a heap table, an index_id of 1 represents a clustered index, which has the same number of rows as the table it indexes - is that right?
There is an ominous phrase in BOL about the row_count being "the approximate number of rows." What is the reason for this caveat and how can I ensure the count is accurate?
I am also wondering if the CROSS APPLY above could be recast as another inner join. I am trying to do this but have not yet succeeded. If I can understand the reasons it would definitely help my understanding.
I will come back to this tomorrow. Thanks again.
MarkD
CROSS APPLY actually is an INNER JOIN, just of a rather different sort. If no records were to come from that query within it, then no records would output from the main portion of the query. If you ever need that relationship to act like a LEFT JOIN, then use OUTER APPLY instead. In order to turn that into an INNER JOIN, you'd have to SELECT the object_id field from the table in the CROSS APPLY query, and use GROUP BY on it within that query, and then remove the WHERE clause and have that become the ON clause for the INNER JOIN. It might not perform anywhere near as well, however. One of the many reasons the APPLY operator was created.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 5, 2017 at 11:23 am
You got the first paragraph right.
About the approximate number of rows, I'd say that the number is pretty accurate. The reason for this warning is that some tables have constant activity and current transactions would affect the number of rows. I'm certain that the number could even change from the time that the dmv is queried to the time the results are shown on screen. If there's no activity in the queried table, the count should be exact.
The CROSS APPLY is there because it's a correlated subquery, but it could also be rewritten as a JOIN.
INNER JOIN ( SELECT s.object_id, SUM(row_count) AS row_count
FROM MyDB.sys.dm_db_partition_stats s
WHERE index_id <= 1
GROUP BY s.object_id) r ON r.object_id = t.object_id;
October 6, 2017 at 10:48 am
Thanks Steve and Luis for the enlightenment.
I was a bit hesitant to ask this question, but your answers have really helped a lot. There's a good atmosphere at SS Central.
I have to say I am also really grateful for Quassnoi's post on CROSS APPLY at https://explainextended.com/2009/07/16/inner-join-vs-cross-apply/ . An awesome post, which does a great job of actually explaining the concept as opoosed to describing it in minute detail (a common failing of BOL).
MarkD
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply