3

I've been using the SQL found here: Find all tables containing column with specified name

to great success. It allows me to find all tables that contain a certain column. My issue is that the database I'm working on seems to have a lot of empty tables (maybe around half of my results are empties). I was wondering if there was a way to modify the code in the link such that empty rows/columns are not presented.Below is the code from the link:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

Thank you,

Community
  • 1
  • 1
  • You could do it using dynamic SQL. Is that an option? – logixologist Jan 18 '17 at 19:17
  • To be honest, I'm not entirely sure what that is. I did a quick look on Google and the post here: http://stackoverflow.com/questions/4165020/what-is-dynamic-sql Says that Dynamic SQL can hurt performance or it isn't always necessary... I'm willing to try, but regular SQL might be better? –  Jan 18 '17 at 19:24
  • @JohnnyBaggadoughnuts dynamic sql is actually pretty good for this kind of thing. Although I'd go with Dan's answer using sys.partitions. – Gareth Lyons Jan 18 '17 at 19:30
  • Is performance a concern? This doesn't seem like you should be running all the time - more like a maintenance task? – D Stanley Jan 18 '17 at 19:36
  • I don't think performance is much of a concern. I'd only run this when I was looking to find something. It's more like maintenance, yes. –  Jan 18 '17 at 19:59
  • The whole idea behind dynamic sql is that you are creating your query initially as a string of text and then executing that string. I will see if I can put something together,. I didn't know about sys.partitions – logixologist Jan 18 '17 at 21:42

1 Answers1

5

Something like this may work without huge effort:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
            ,p.rows
FROM sys.columns c
INNER JOIN sys.tables t
ON c.object_id = t.object_id
INNER JOIN sys.partitions p
on t.object_id = p.object_id
WHERE       c.name LIKE '%p%'
            AND p.rows > 0
ORDER BY    TableName
            ,ColumnName;

Just note that sys.partitions isn't guaranteed to be accurate about row counts; sys.dm_db_index_physical_stats may be better (see https://dba.stackexchange.com/questions/55124/how-accurate-is-the-sys-partition-rows-column). This might give you a better count but may have more locking issues if you're using AlwaysOn:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
            ,ips.record_count
FROM sys.columns c
INNER JOIN sys.tables t
ON c.object_id = t.object_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), t.object_id, null, null, 'DETAILED') ips
WHERE       c.name LIKE '%p%'           
            AND ips.record_count > 0
ORDER BY    TableName
            ,ColumnName;

If you really need 100% reliability on this, you'd have actually do a COUNT(*) on each table (using Dynamic SQL) you want to check, but this is probably good enough.

Community
  • 1
  • 1
Dan Field
  • 20,885
  • 5
  • 55
  • 71
  • 1
    I'd say sys.partitions is more than accurate enough for this kind of check (zero vs. non-zero). – Gareth Lyons Jan 18 '17 at 19:33
  • Very likely yes, but if you're outputing a row count it may be helpful to have it be more accurate than less – Dan Field Jan 18 '17 at 19:35
  • Thanks Dan, this should work well. There's a column for rows now, which is necessary, but I can export to CSV and filter that out with Excel or something. I'll only be running this every few weeks or months so it's not a huge cocnern. Thanks again, –  Jan 18 '17 at 20:00
  • If you don't need that column outputted, you can just comment it out from the select or remvoe it entirely (e.g. remove `p.rows` or `ips.record_count`). If this answers your question, consider marking the question as answered (the check box below the vote selector) for future readers. – Dan Field Jan 18 '17 at 20:20