Loop through list of tables to find size and columns?

Question

Loop through list of tables to find size and columns?

jporter 4363

SSC Veteran

Points: 246
More actions
November 11, 2016 at 8:04 am

#401027

Hi everyone,
I posted here the other day and the feedback was very helpful! Right now I have a list of 55 table names and I would like to write a script to loop through those tables and get the row count as well as see if a modified date column exists in order to see which tables would be easier to ETL and which ones wouldn't.
Has anyone had any similar experiences or could point me in the right direction to write such a script?
I'm using SQL Server 2008 R2 and SSMS.
Many thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You don't need a loop to get table sizes. Just type "table size query" into your favourite search engine and you'll get plenty of pointers to what to do. For the second part of your question, you can query sys.columns (or INFORMATION_SCHEMA.COLUMNS) to get a list of columns that have a date-related data type. I'm not sure how you'll distinguish between date modified and data created and any other kind of date without just eyeballing the results.

John

jporter 4363 SSC Veteran Points: 246 More actions · Answer 2

I'm sorry, I must not have been clear in my question. I need a loop to loop through a list of 55 tables that I have. The end result I'm looking for is a query that will return each table along with its row count and if a ModifiedDate column exists within that table.

This is the query I used to get my list of tables from a stored procedure:

SELECT DISTINCT

[object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name

, o.type_desc

FROM sys.dm_sql_referenced_entities ('dbo.spMyStoredProcedureHere', 'OBJECT') d

JOIN sys.objects o ON d.referenced_id = o.[object_id]

WHERE o.[type] IN ('U', 'V')

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

No, you still don't need a loop. Put your list of tables in a temp table and join that to the results of one of the queries that you discovered when you did the search I suggested. You can then left join the results of that to sys.columns to find out whether each one has a column called ModifiedDate.

John

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 4

Quick Dynamic SQL solution

😎

USE TEEST;

GO

SET NOCOUNT ON;

DECLARE @TEMPLATE NVARCHAR(MAX) = N'

UNION ALL

SELECT

''{{@SCHEMA_NAME}}.{{@TABLE_NAME}}'' AS TABLE_NAME

,COUNT(*) AS ROW_COUNT

,{{@DATE_COLUMN}} AS MAX_MODIFIED_DATE

FROM {{@SCHEMA_NAME}}.{{@TABLE_NAME}} X

';

DECLARE @SQL_STR NVARCHAR(MAX) =

( SELECT

STUFF((

SELECT

REPLACE(

REPLACE(@TEMPLATE,N'{{@SCHEMA_NAME}}',OBJECT_SCHEMA_NAME(STAB.object_id))

,N'{{@TABLE_NAME}}',STAB.name)

,N'{{@DATE_COLUMN}}',ISNULL(N'MAX(' + SCOL.name + N')',N'NULL'))

FROM sys.tables STAB

LEFT OUTER JOIN sys.columns SCOL

ON STAB.object_id = SCOL.object_id

AND SCOL.NAME LIKE N'%M%DATE%'

FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,11,N'')

);

EXEC (@SQL_STR);