Sql help

1

I have a list of the tables that has the common column lets say id from the main table.Lets say tableA is the main table and Id is the common column.
Now I am trying to compare the column id from all the other tables against tableA where id is null on tableA.

since I have so many tables,I don't want to do one by one.
Thanks in advance
Here what I have so far--

DECLARE @TableName NVARCHAR(MAX);

DECLARE @tbls table ( TableName VARCHAR(100) );
INSERT INTO @ tbls
( TableName
)
SELECT TABLE_NAME
FROM test.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'id';

WHILE EXISTS ( SELECT TableName
FROM @tbls )
BEGIN
SELECT TOP 1
@TableName = TableName
FROM tbls;

--need a dynamic code here TO GET LIST OF ALL the tbls something like

SELECT tablename FROM tableA
LEFT JOIN @tbls
ON tableA.ID=@tbls.ID
WHERE tableA.ID IS NULL

DELETE FROM @tbls WHERE TableName = @TableName

END

2

I would just generate the queries and copy/paste/F5 the output instead of looping. I do the looping when this is something I am putting into a script/job/etc. If this is a one-time thing, I just generate the code instead.

SELECT 'SELECT ' + TABLE_NAME + ' FROM TableA LEFT JOIN ' + TABLE_NAME + ' ON TableA.id = ' + TABLE_NAME + '.id WHERE TableA.id IS NULL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'id';

1 Like
3

Awsome! script worked very well.Thank you Tara

4

Tara, just curiosity,I am not good at dynamic sql query.Just wondering if there are any good sites to learn from the very beginning.All I could find is the code help to generate the dynamic query but not the tutorial for learning dynamic sql query.

5

Take a look at Erland's article here

Itzik Ben-Gan's books have very good coverage of dynamic SQL as well. For example this.

1 Like
6

Thank you James.