January 5, 2011 at 11:12 am
I have a database with a bunch of user tables. I need to scroll through the table names and find (and output) tables names that satisfy a condition. The condition is that the table does not have a row with date_stamp = '1-4-2011'. In practice I am trying to find out tables that are missning the row (with date_stamp = '1-4-2011').
So I use a cursor as follows:
DECLARE @Table NVARCHAR(200),@Column NVARCHAR(200),@Sql NVARCHAR(2000)
DECLARE Table_Cursor CURSOR FOR
SELECT so.Name AS 'Table'
FROM sys.sysobjects so
LEFT OUTER JOIN sys.syscolumns sc
ON sc.id = so.id
WHERE so.type = 'u'AND sc.name = 'Pk_Dmnd_On'
--or use AND sc.xtype = 40 for all columns with a data type of Date
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor
INTO @Table
WHILE @@FETCH_STATUs = 0
BEGIN
/* set @sql = ?? -- test if not exists the row with date_stamp = '1-4-2011 -- If true output the table name */
EXECUTE SP_EXECUTESQL @sql
FETCH NEXT FROM Table_Cursor INTO @Table
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
I am thinking I need to use "not exists" or "not in", but not sure yet about the query.
January 5, 2011 at 11:51 am
Create a temp table. Insert the table name (from the variable) into it, if no rows exist that fit the criteria. You can test for that with a Not Exists condition.
Something like this:
if not exists (select 1 from ...table name... where column between ...date range ...) insert into #T (TName) Values (@Variable);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 2:10 pm
if not exists
(select date_stamp from myTable Where date_stamp = '1-4-2011')
Begin
print 'myTable'
End
Now I need to figure out how to put it in a cursor, so it cna be executed for each table?
January 5, 2011 at 2:30 pm
Turn that query into a string in a variable. Replace "MyTable" with the value from the cursor. Execute it as dynamic SQL.
declare @TableName varchar(255), @CMD varchar(1000);
declare Tabless cursor static for
select name
from sys.tables;
open Tables;
fetch first from Tables into @TableName;
while @@fetch_status = 0
begin
set @CMD = 'if exists (select 1 from dbo.[' + @TableName + '] where column = ''1/4/2011'') print ' + @TableName + ';'
exec(@CMD);
fetch next from Tables into @TableName;
end;
It'll be something like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 11:11 am
Thanks for the feedback. I have adapted your solution, and it works great. This the query I am using now.
use Datbase1
go
DECLARE @Table NVARCHAR(200),@Column NVARCHAR(200),@Sql NVARCHAR(2000)
DECLARE Table_Cursor CURSOR FOR
SELECT so.Name AS 'Table'
FROM sys.sysobjects so
LEFT OUTER JOIN sys.syscolumns sc
ON sc.id = so.id
WHERE so.type = 'u'AND sc.name = 'Date_Stamp'
OPEN Table_Cursor
FETCH next FROM Table_Cursor
INTO @Table
WHILE @@FETCH_STATUs = 0
BEGIN
set @sql = 'if not exists (select 1 from dbo.[' + @Table + '] where Date_Stamp = ''1/12/2011'') '
set @sql = @sql + 'print ''' + @Table + ''''
--print @sql
--print '-------------------------'
EXECUTE SP_EXECUTESQL @sql
FETCH NEXT FROM Table_Cursor INTO @Table
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
January 12, 2011 at 11:14 am
On the cursor definition, you can change that from a Left Outer join to an Inner Join. The Where clause forces that anyway.
Glad I could help out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 11:45 am
Yes. You are right.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply