Sql help

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

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

Awsome! script worked very well.Thank you Tara

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.

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

Thank you James.