August 25, 2011 at 4:20 pm
I have a column called InsertedItemWhen on all tables that begin with CMS_
I want to go though all the tables that being with CMS_ and look for data entered after a certain date/time
August 26, 2011 at 1:44 am
amitchaudhury (8/25/2011)
I have a column called InsertedItemWhen on all tables that begin with CMS_I want to go though all the tables that being with CMS_ and look for data entered after a certain date/time
This should get you started:
sp_msforeachtable 'if CHARINDEX(''.[CMS_'', ''?'') > 0 BEGIN SELECT ''?'', COUNT(*) FROM ? WHERE InsertedItemWhen > ''20100101'' END'
August 26, 2011 at 1:50 am
Or some dynamic SQL
DECLARE @sql VARCHAR(MAX)
SELECT @sql = ISNULL(@sql,'') + 'SELECT ''' + name + ''',COUNT(*) FROM [' + SCHEMA_NAME(schema_id) + '].[' + name +'];' + CHAR(10)
FROM sys.objects WHERE name LIKE 'CMS_%';
--PRINT @sql
EXEC(@sql)
August 26, 2011 at 7:04 am
Thanks Guys !!!! It works, however there is one more condition... some of the tables that begin with CMS_ do not have the column ItemCreatedWhen in them.. can i add a condition that when it goes through all the tables beginning with CMS_ and if it does not find the column ItemInsertedWhen, it should skip it and move to the next table...
August 26, 2011 at 7:12 am
Join with the sys.columns table
August 26, 2011 at 7:17 am
Something like this:
SELECT DISTINCT
o.name
FROM
sys.columns c
JOIN
sys.objects o ON c.object_id = o.object_id
WHERE
c.name = 'ItemInsertedWhen'
AND
o.name LIKE 'CMS_%'
AND
o.type = 'U'
August 26, 2011 at 7:21 am
Or use one of the INFORMATION_SCHEMA views and avoid the need to join at all:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ItemInsertedWhen'
John
August 26, 2011 at 7:47 am
John Mitchell-245523 (8/26/2011)
Or use one of the INFORMATION_SCHEMA views and avoid the need to join at all:John
Nice one John
August 26, 2011 at 7:48 am
can you please past some sample code that i can follow ? i am not an SQL expert like you guys 🙂
August 26, 2011 at 9:03 am
amitchaudhury (8/26/2011)
can you please past some sample code that i can follow ? i am not an SQL expert like you guys 🙂
Try this:
DECLARE @sql VARCHAR(MAX)
SELECT @sql = ISNULL(@sql,'') +
'SELECT TableName=''' + TABLE_NAME + ''', Rows = COUNT(*) FROM ' +
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) +
' WHERE ItemInsertedWhen >= ''20100101'';' + CHAR(10)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME = 'ItemInsertedWhen'
EXEC(@sql)
SET @sql= ''
SELECT @sql = ISNULL(@sql,'') +
'SELECT TableName=''' + TABLE_NAME + ''', * FROM ' +
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) +
' WHERE ItemInsertedWhen >= ''20100101'';' + CHAR(10)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME = 'ItemInsertedWhen'
EXEC(@sql)
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply