identity ranges - Page 47

Different Ways of Online Brand Management

Brand image refers to a unique business identity carried by an organization. It refers to an image perceived by the potential customers as unique solution to their problems.

Resolving data redundancy up front

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 3169 results on 127 pages for 'identity ranges'.

Page 47/127 | < Previous Page | 43 44 45 46 47 48 49 50 51 52 53 54 | Next Page >

- by okeofs

- by Clint Edmonson

- by kyap

- by GrumpyOldDBA

- by Your DisplayName here!

- by Michelle Kimihira

- by Michael Zilberstein

- by Michael Zilberstein

- by Shashidhar Malyala

- by silla

- by THE

< Previous Page | 43 44 45 46 47 48 49 50 51 52 53 54 | Next Page >