CODEWITHSUNDEEP
sqlsql-serverdenormalization
user3558286
user3558286

Reputation: 617

Select by a key for all associated records from a denormalizing database

I am using SQL server 2012. I just have access to a database that does not have any relationships yet. I don't know much about SQL script. I want to see all the records that associate to EmployeeID 102 from all tables that have column EmployeeID. E.g. 

EmployeeID    LastName     FirstName   (from table Employees)
102           Jonh         Smith
EmployeeID    HireDate                 (from table EmploymentRecords)
102           2/1/2014
EmployeeID    Monthly Salary           (from table Salary)
102           2000
and so on

I tried the following script, but the Result window become closed and on Message window it just said "Command(s) completed successfully". How can I get the above results. Thanks in advance.

DECLARE tnames_cursor CURSOR
FOR
  SELECT TABLE_NAME 
  FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename

if (select count(*) from information_schema.columns where  table_name = @tablename and     Column_name ='EmployeeID') > 0

BEGIN   

  SELECT @tablename = RTRIM(@tablename) 
  EXEC ('SELECT *  FROM '+ @tablename +'where  EmployeeID = 102')
END

Upvotes: 2

Views: 60

Answers (2)

Martin K.
Martin K.

Reputation: 1060

Basically the immediate problem is that you are not looping through the whole result set.

DECLARE tnames_cursor CURSOR
FOR
  SELECT TABLE_NAME 
  FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename

while @@FETCH_STATUS=0
begin

    if (select count(*) from information_schema.columns where  table_name = @tablename and     Column_name ='EmployeeID') > 0
    begin

        SELECT @tablename = RTRIM(@tablename) 
        EXEC ('SELECT *  FROM '+ @tablename +'where  EmployeeID = 102')
    end


    FETCH NEXT FROM tnames_cursor INTO @tablename
end
close tnames_cursor
deallocate tnames_cursor

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270733

This statement will probably prevent everything from working:

EXEC ('SELECT *  FROM '+ @tablename +'where  EmployeeID = 102')

You need a space after the table name:

EXEC ('SELECT *  FROM '+ @tablename +' where  EmployeeID = 102')

In addition, your cursor logic seems off. You should be checking for @@FETCH_STATUS and then closing and deallocating the cursor.

Follow the example at the end of the documentation.

Upvotes: 2

Related Questions