SQL -Query to find a specific column in all database tables, but no null values
I'm not a SQL expert. I need to find a column that is present in all tables in a database, but that does not have nulls stored for that particular record.
Below is a query that fetches me all tables with column name:
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'classPK'
+3
source to share
2 answers
Try it...
declare @q nvarchar(1000);
declare cur_Select cursor for
select 'select * from ['+tab.name+'] where ['+col.name+'] is not null'
from sys.tables tab
join sys.columns col
on tab.object_id = col.object_id
where col.name like 'name'
open cur_Select
fetch next from cur_Select into @q
while @@FETCH_STATUS = 0
begin
exec sp_executesql @q
fetch next from cur_Select into @q
end
close cur_Select
deallocate cur_Select
+2
source to share
Try with this statement:
DECLARE @TABLE_NAME NVARCHAR(MAX);
DECLARE @COLUMN_NAME NVARCHAR(MAX);
DECLARE @crs CURSOR;
DECLARE @query NVARCHAR(MAX);
set @crs = CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'CD_CIC_PROD%'
BEGIN TRY
OPEN @crs
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM @crs INTO
@TABLE_NAME, @COLUMN_NAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @query = 'SELECT * FROM ['+@TABLE_NAME+'] WHERE ['+@COLUMN_NAME+'] IS NOT NULL'
EXEC sp_executesql @query
FETCH NEXT FROM @crs INTO
@TABLE_NAME, @COLUMN_NAME
END
0
source to share