Select query for multiple tables from system configuration(sys.tables) with specific columns

1

Hi All,

I have 70 plus tables from my database. I can get the list of all tables by using sys.tables with specific column name. I should get the null values of that specific column for all tables from my database. I am new to SQL. Can anyone please guide me on this. I got the list of tables by using the below query.

SELECT OBJECT_SCHEMA_NAME (c.object_id) SchemaName,
o.Name AS Table_Name,
c.Name AS Field_Name,
t.Name AS Data_Type,
t.max_length AS Length_Size,
t.precision AS Precision
FROM sys.columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
LEFT JOIN sys.types t on t.user_type_id = c.user_type_id
WHERE o.type = 'U'
-- and o.Name = 'YourTableName'
ORDER BY o.Name, c.Name

Thanks,
Arun Kumar Durairaj,
darunk90@gmail.com.

2

Welcome. So if in one table there are 50 rows with null value of the specific column, what columns do you want returned from each table?

3

Custom defined columns as per requirements.

4

So you want to find all records in all tables where column @xyz is null.

5

Yes.

6

try this for starters and see what is inside that #kampataboo table. All tables might not have the same custom defined tables and same column data type. so this could ugly very fast or might end up in kludgy solution. What is the end goal here? Just find null values?

Notice I started out with @columnName varchar(150) = 'vmid'; In there plug a column that you know could be null just to give this a start and post back here.

use yourDatabase
go

declare @findNullQuery varchar(max), @parmtableName varchar(150), 
@parmcolumnName varchar(150), 
 @tableName varchar(150), @columnName varchar(150) = 'vmid';


create table #kampataboo(tablename varchar(150), columnname varchar(150), query varchar(max));

DECLARE db_cursor CURSOR FOR 
select t.name, c.name, 'select ''' + t.name + ''' as tablename, ''' + c.name + ''' as columnname from ' + t.name + ' where ' +  c.name + ' is null ' 
  from sys.tables t 
  join sys.columns c on t.object_id = c.object_id 
 where ( @columnName is null or c.name = @columnName )
   and ( @tableName is null or t.name = @tableName )

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tableName  , @columnName, @findNullQuery

WHILE @@FETCH_STATUS = 0  
BEGIN  
	--select @findNullQuery
	  insert into #kampataboo
	  select @tableName, @columnName, @findNullQuery

      FETCH NEXT FROM db_cursor INTO @tableName , @columnName, @findNullQuery
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

select * From #kampataboo

drop table #kampataboo
7

So, as per your answer, you're creating a temporary table ans storing those values into that table. Once, we displayed the results, you're dropping that table. am I right?

8

yes that is just for test purposes. you can ignore that

9

ok Sure. I will check it and let you know on this. Thanks.

10

Hi,

How I can execute those set of queries in sql. I meant, all sql query and save the data from this "findNullQuery" column.

Thanks.

11

Issue resolved. Thanks. Is there any possibility to store those data into Temp table or Hash tables? If yes, can you please guide me on this.

12

hi

this is another approach !!!! hope its what you are looking for !!!
:slight_smile: :slight_smile:

select table_name,column_name,'NULL' as NULLABLE
from information_schema.columns
where is_nullable = 'YES'

image

1 Like
13

Just add
Insert into forensics
Exec(@findNullQuery)

But remember different tables might have different data type for same column so you need convert to a common data type of make destination column be nvarchar(max)