Testing All Fields for Data Table Wide

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Testing All Fields for Data Table Wide

Author

Topic

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-17 : 15:04:50

I am trying to run a query for all columns in a table... basically, if the field has data, I want it to return 'Data' or '1' or whatever. If it doesn't have data in any record, then return null or '0'.

Example


column1   column2   column3
------
fred       fred        null
george     george      null

For column1 and column2 it would return 'data'; for column3 null.

I am wanting something that I can go through each table quickly, run the query and know whether there is data in each field... preferably all I'd have to change is the databasename.dbo.tablename in the query and not change anything else.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 15:30:08

Something like this might do it:


declare cur cursor for
select quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name) as tblname from sys.tables

open cur
declare @tblname sysname;
declare @sql nvarchar(max);

fetch next from cur into @tblname
while @@FETCH_STATUS = 0 begin
   set @sql = 'SELECT ' + stuff(
   (
	   select ', CASE WHEN ' + quotename(name) + 'IS NOT NULL THEN 1 END AS ' + quotename(name) + ' '
	   from sys.columns where object_id = object_Id(@tblname)
	   for xml path('')
   ),1,1,'') + 'FROM ' + @tblname
   exec sp_executeSql @sql	
   fetch next from cur into @tblname
end

close cur
deallocate cur

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-17 : 15:56:02

This is definitely pretty close and nice coding but it is a little more elaborate than what I need. I tried editing the code but failed.

Basically, I would only run this by manually typing in the database name, schema, and table name per table. Then if a field is null or blank, then 0; if there is data 1.

Your query is a bit more complex and queries the entire database in one go and doesn't account for blanks... then it is somewhat hard to distinguish which tables the rows were without going through each individual table name...

With that being said I'm going to use your example and hopefully I can figure out some way to simplify it to what I am looking for so you definitely gave me a good start XD

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 16:00:32

great! Good luck! Note that you can use sys.columns to get at the datatype and know if to test for blanks or zeros or whatever.

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-17 : 16:32:30

Why would this fail out?


declare cur cursor for
select quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name) as tblname from sys.tables

open cur
declare @tblname sysname;
declare @sql nvarchar(max);

fetch next from cur into @tblname
while @@FETCH_STATUS = 0 begin
   set @sql = 'SELECT ' + stuff(
   (
	   select ', CASE WHEN ISNULL(' + quotename(name) + ', '''') <> '''' THEN 1 END AS ' + quotename(name) + ' '
	   from sys.columns where object_id = object_Id(@tblname)
	   for xml path('')
   ),1,1,'') + 'FROM ' + @tblname
   exec sp_executeSql @sql	
   fetch next from cur into @tblname
end

close cur
deallocate cur

With this error:

Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near ';'.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 18:02:54

Since we're using FOR XML, Sql encodes the <> signs to %lt; and > To get the <> back, change the end of the subquery to this:


for xml path(''), type
   ).value('.', 'nvarchar(max)'),1,1,'') + 'FROM ' + @tblname

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-19 : 12:20:14

Oh okay... that makes sense. I was able to modify this statement for the specific table

declare cur cursor for
select quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name) as tblname from sys.tables 
where quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name) = '[dbo].[TableName]

My next question would be how would you get the query results in the order of what the table is in the database versus alphabetical order?

And is there a way to easily get it to pull back one record per table that would pull a top value if it was '1'? That way I could quickly just do a snapshot view of it whether there was data in any of the rows in the entire table with one quick view.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 12:34:29

not sure what you mean by this "in the order of what the table is in the database". However, you can add an ORDER BY clause to the cursor definition, if that will help.

you can add TOP(1) to the select statement in the loop. Note however that TOP(1) without an ORDER BY statement is non-deterministic. You may get a different result on the same data with every execution.

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-19 : 12:44:20

Right now it currently does it by alphabetical order unrelated to table structure for the query results IE:

QUERY RESULTS:


Address     FirstName      LastName

Table Structure


LastName     FirstName      Address

I was able to add the TOP 1 in the select statement. I know that before I have done something similar to this using row_number over and partitioning but I have no idea how to implement that via XML. 

Some code in the past was written like this in the FROM statement then in the actual select statement CASE when mnameSeq = 1 THEN blah:

FROM 
(
SELECT *
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(Mname) DESC) AS mnameSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(Scars) DESC) AS ScarSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(SSN) DESC) AS SSNSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(ETHNIC) DESC) AS HispanicSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(HEIGHT) DESC) AS HeightSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(WEIGHT) DESC) AS WeightSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(HAIR) DESC) AS HairSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(EYES) DESC) AS EyeSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(oln) DESC) AS IDNOSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(state_oln) DESC) AS IDStateSeq
	,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY age DESC) AS ageSeq
FROM [DatabaseName].[dbo].[PersonTable]) Mni
GROUP BY ID, fname , lname , DOB_Conv, sex , race

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 13:01:19

OIC what you mean. Try changing the query in the loop to:


select ', CASE WHEN ' + quotename(name) + 'IS NOT NULL THEN 1 END AS ' + quotename(name) + ' '
	   from sys.columns where object_id = object_Id(@tblname)
           order by column_id
	   for xml path('')
etc.

Note the "order by column_id" in there.

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-19 : 14:15:36

That worked great... is there anyway to do the select top 1 and then do a row partition and pick the record with '1' so there'd just be one return per table that has one row that shows whether something has data in it or not?

Otherwise all of this would kind of be a bust since it will still be me manually looking for data in each row for each table.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 14:45:45

I think you mean pick the column with '1', not the record (row in row set) with 1, correct?

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-19 : 14:48:28

Yeah, pretty much like this:

 
col1   col2   col3   col4
---    ---    ---    ----
1      null   null     1
null   null    1       1
null   null    null    1
1      null    null    1

Then all of that would run to 

col1   col2   col3   col4
---    ---    ---    ---
1      null    1      1

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 14:59:05

In that case, instead of returning '1', just return the column name. IOW make the CASE statement like this (using the same techniques). I DIDN'T TEST THIS!


   set @sql = 'SELECT CASE' +
   (
	   select ' WHEN ISNULL(' + quotename(name) + ', '''') <> '''' THEN ' + quotename(name)
	   from sys.columns where object_id = object_Id(@tblname)
	   for xml path('')
   ) + ' END AS ColIWantToSee FROM ' + @tblname

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-19 : 15:11:01

I tried running and got a similar error when I tried to use ISNULL() earlier...

Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near ';'.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 16:02:42

need to wrap the colname in quotes:

THEN ' + ''name''

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-19 : 16:31:00

[code]
set @sql = 'SELECT CASE' +
(
select ' WHEN ISNULL(' + quotename(name) + ', '''') <> '''' THEN ' + ''name''
from sys.columns where object_id = object_Id(@tblname)
for xml path('')
) + ' END AS ColIWantToSee FROM ' + @tblname

[/code]

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ''.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-19 : 17:52:33

Maybe you figured this out already:


select ', CASE WHEN ISNULL(' + quotename(name) + ', '''') <> '''' THEN ' + ''''+name+''''
	   from sys.columns where object_id = object_Id(@tblname)
	   for xml path(''),type

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2015-02-20 : 11:10:46

[code]
fetch next from cur into @tblname
while @@FETCH_STATUS = 0 begin
set @sql = 'SELECT CASE' +
(
select ', CASE WHEN ISNULL(' + quotename(name) + ', '''') <> '''' THEN ' + ''''+name+''''
from sys.columns where object_id = object_Id(@tblname)
for xml path(''),type
) + ' END AS ColIWantToSee FROM ' + @tblname

--set @sql = 'SELECT' + stuff(
--(
-- select ', CASE WHEN ' + quotename(name) + 'IS NOT NULL THEN 1 END AS ' + quotename(name) + ' '
-- from sys.columns where object_id = object_Id(@tblname)
--order by column_id
--for xml path(''), type
--).value('.', 'nvarchar(max)'),1,1,'') + 'FROM ' + @tblname
exec sp_executeSql @sql
fetch next from cur into @tblname
end
[/code]

Now this error occurs:

Msg 402, Level 16, State 1, Line 12
The data types varchar and xml are incompatible in the add operator.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 12:26:47

You still need .value('.', 'nvarchar(max)') at the end of the query, like before.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 12:27:23

PS I'm trying NOT to give you a complete solution here. I'm hoping that you will pick up the principles and run with them.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources