liron loop - Page 75 - Developer IT

Resolving data redundancy up front

- by okeofs

Introduction As all of us do when confronted with a problem, the resource of choice is to ‘Google it’. This is where the plot thickens. Recently I was asked to stage data from numerous databases which were to be loaded into a data warehouse. To make a long story short, I was looking for a manner in which to obtain the table names from each database, to ascertain potential overlap. As the source data comes from a SQL database created from dumps of a third party product, one could say that there were +/- 95 tables for each database. Yes I know that first instinct is to use the system stored procedure “exec sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'”. However, if one stops to think about this, it would be nice to have all the results in a temporary or disc based table; which in itself , implies additional labour. This said, I decided to ‘re-invent’ the wheel. The full code sample may be found at the bottom of this article. Define a few temporary tables and variables declare @SQL varchar(max); declare @databasename varchar(75) /* drop table ##rawdata3 drop table #rawdata1 drop table #rawdata11 */ -- A temp table to hold the names of my databases CREATE TABLE #rawdata1 ( database_name varchar(50) , database_size varchar(50), remarks Varchar(50) ) --A temp table with the same database names as above, HOWEVER using an --Identity number (recNO) as a loop variable. --You will note below that I loop through until I reach 25 (see below) as at --that point the system databases, the reporting server database etc begin. --1- 24 are user databases. These are really what I was looking for. --Whilst NOT the best solution,it works and the code was meant as a quick --and dirty. CREATE TABLE #rawdata11 ( recNo int identity(1,1), database_name varchar(50) , database_size varchar(50), remarks Varchar(50) ) --My output table showing the database name and table name CREATE TABLE ##rawdata3 ( database_name varchar(75) , table_name varchar(75), ) Insert the database names into a temporary table I pull the database names using the system stored procedure sp_databases INSERT INTO #rawdata1 EXEC sp_databases Go Insert the results from #rawdata1 into a table containing a record number #rawdata11 so that I can LOOP through the extract INSERT into #rawdata11 select * from #rawdata1 We now declare 3 more variables: @kounter is used to keep track of our position within the loop. @databasename is used to keep track of the’ current ‘ database name being used in the current pass of the loop; as inorder to obtain the tables for that database we need to issue a ‘USE’ statement, an insert command and other related code parts. This is the challenging part. @sql is a varchar(max) variable used to contain the ‘USE’ statement PLUS the’ insert ‘ code statements. We now initalize @kounter to 1 . declare @kounter int; declare @databasename varchar(75); declare @sql varchar(max); set @kounter = 1 The Loop The astute reader will remember that the temporary table #rawdata11 contains our database names and each ‘database row’ has a record number (recNo). I am only interested in record numbers under 25. I now set the value of the temporary variable @DatabaseName (see below) .Note that I used the row number as a part of the predicate. Now, knowing the database name, I can create dynamic T-SQL to be executed using the sp_sqlexec stored procedure (see the code in red below). Finally, after all the tables for that given database have been placed in temporary table ##rawdata3, I increment the counter and continue on. Note that I used a global temporary table to ensure that the result set persists after the termination of the run. At some stage, I plan to redo this part of the code, as global temporary tables are not really an ideal solution. WHILE (@kounter < 25) BEGIN select @DatabaseName = database_name from #rawdata11 where recNo = @kounter set @SQL = 'Use ' + @DatabaseName + ' Insert into ##rawdata3 ' + + ' SELECT table_catalog,Table_name FROM information_schema.tables' exec sp_sqlexec @Sql SET @kounter = @kounter + 1 END The full code extract Here is the full code sample. declare @SQL varchar(max); declare @databasename varchar(75) /* drop table ##rawdata3 drop table #rawdata1 drop table #rawdata11 */ CREATE TABLE #rawdata1 ( database_name varchar(50) , database_size varchar(50), remarks Varchar(50) ) CREATE TABLE #rawdata11 ( recNo int identity(1,1), database_name varchar(50) , database_size varchar(50), remarks Varchar(50) ) CREATE TABLE ##rawdata3 ( database_name varchar(75) , table_name varchar(75), ) INSERT INTO #rawdata1 EXEC sp_databases go INSERT into #rawdata11 select * from #rawdata1 declare @kounter int; declare @databasename varchar(75); declare @sql varchar(max); set @kounter = 1 WHILE (@kounter < 25) BEGIN select @databasename = database_name from #rawdata11 where recNo = @kounter set @SQL = 'Use ' + @DatabaseName + ' Insert into ##rawdata3 ' + + ' SELECT table_catalog,Table_name FROM information_schema.tables' exec sp_sqlexec @Sql SET @kounter = @kounter + 1 END select * from ##rawdata3 where table_name like '%SalesOrderHeader%'

Search Results

Search found 8027 results on 322 pages for 'liron loop'.

Page 75/322 | < Previous Page | 71 72 73 74 75 76 77 78 79 80 81 82 | Next Page >

- by okeofs

- by Ben

- by Geoff Ongley

- by neurolysis

- by JoshMaurice

- by Amey Jah

- by Postman

- by The chicken in the kitchen

- by Kyrol

- by jshin47

- by Karl Richter

- by RocketGoal

- by Jonny

- by daver

- by SoulBeaver

- by randallmeadows

- by Herman Plani Ginting

- by Benlitz

- by Jamie Dixon

- by Incognito

- by David Kroukamp

- by changokun

< Previous Page | 71 72 73 74 75 76 77 78 79 80 81 82 | Next Page >