Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Home-grown Data Mining in SQL Server-4 : Page 4


advertisement
Code for the Solution
I used the following code to query tables in a database for values I knew, but whose location I didn't know:

DECLARE @tablename varchar(50) DECLARE @columnname varchar(50) DECLARE @datatype varchar(50) DECLARE @strsql Nvarchar(500) SET NOCOUNT ON DECLARE t1 CURSOR FOR SELECT table_name FROM information_schema.tables OPEN t1 FETCH NEXT FROM t1 INTO @tablename WHILE @@fetch_status = 0 BEGIN DECLARE c1 CURSOR FOR SELECT column_name, data_type FROM information_schema.columns WHERE table_name = @tablename OPEN c1 FETCH NEXT FROM c1 INTO @columnname, @datatype WHILE @@fetch_status = 0 BEGIN IF @datatype = 'VARCHAR' or @datatype ='NVARCHAR' or @datatype ='NCHAR' or @datatype ='CHAR' BEGIN SET @strsql = N' SELECT TOP 10' + @tablename+N'.'+@columnname + N' FROM ' + @tablename + N' WHERE ' +@columnname + N' IN (''AC'',''VR'',''AB'')' EXEC sp_executesql @strsql END FETCH NEXT FROM c1 INTO @columnname, @datatype END CLOSE c1 DEALLOCATE c1 FETCH NEXT FROM t1 INTO @tablename END CLOSE t1 DEALLOCATE t1 SET NOCOUNT OFF

I began by creating placeholder variables to store the values fetched from the table name or outer cursor. These feed the inner cursor that queries the table name's columns (in the line WHERE table_name = @tablename).



The code then tests for the datatype, so I don't bother asking about columns that are money or date time, for example. The other nice thing is using sp_executesql to run the string created from the variables collected in the cursor. I included the TOP modifier in hopes of getting the best execution plan, rather than have an already inefficient process wait around for table scans. TOP is a shot in the arm that can reduce execution time:

SET @strsql = N' SELECT TOP 10' + @tablename+N'.'+@columnname + N' FROM ' + @tablename + N' WHERE ' +@columnname + N' IN (''AC'',''VR'',''AB'')' EXEC sp_executesql @strsql

Found: Four Needles in a Haystack
This idea could also be expressed as a set-based solution by wrapping a temporary table insert in a WHILE loop. This is a technique that was used before cursors existed. Since that route requires a temporary table, I chose this way to find my needle in a haystack. I hope you can find some use for it too.



Drew Georgopulos is the database architect for GoAmerica Communications. He moonlights as an adjunct professor of computer science at Marymount College of Fordham University, teaching systems analysis and relational database design. .
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date