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