SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Populate a Query with a list of Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 03/08/2013 :  10:11:15  Show Profile  Reply with Quote
Good morning, I have a query that produces a list of NULL value fields in a table. What I would like to do is have the user (me) select a table from a list of the tables in the database, or worst generate a list of all tables & fields that contain all NULL values (in other words the fields are not being used).

Here is the initial query

DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('saleslogix.sysdba.c_acct_db_data')
OPEN crs
DECLARE @name sysname
FETCH NEXT FROM crs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('SELECT ''' + @name + ''' WHERE NOT EXISTS (SELECT * FROM saleslogix.sysdba.c_acct_db_data WHERE ' + @name + ' IS NOT NULL)')
FETCH NEXT FROM crs INTO @name
END
CLOSE crs
DEALLOCATE crs

Anybody done this already, thanks in advance.

Bryan Holmstrom

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/08/2013 :  12:02:12  Show Profile  Reply with Quote
when you say "generate a list of all tables & fields (sic) that contain all NULL values (in other words the fields (sic) are not being used)" Do you mean where a column has a null value for every row, not just that that column is nullable?
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 03/08/2013 :  12:06:00  Show Profile  Reply with Quote
This will give you all the tables and the fields where a null exists, you can modify it to give you only the fields where only nulls exists

SET NOCOUNT ON
CREATE TABLE #Nulls(TableName varchar(100),ColumnName varchar(100))
select
'IF EXISTS (SELECT *
FROM Summary.' + TABLE_NAME+
' WHERE [' + COLUMN_NAME + '] IS NULL)
INSERT INTO #Nulls
SELECT ' + '''' + REPLACE('!','!',TABLE_SCHEMA)+ ''' ,'
+ '''' + REPLACE('?','?',TABLE_NAME) + ''' ,'
+ '''' + REPLACE('!','!',COLUMN_NAME)+ ''''+ char(10)

from information_schema.columns dbs
where is_nullable = 'YES'

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/08/2013 :  12:08:30  Show Profile  Reply with Quote
Argh, missed glossed over the IS NOT NULL part of the EXISTS clause..

I don't know of a good way to get that information. But, to help speed things up you could use the INFORMATION_SCHEMA views to help eliminate checking columns that can't be null:
SELECT 
	COLUMN_NAME
FROM 
	INFORMATION_SCHEMA.COLUMNS
WHERE 
	TABLE_SCHEMA = 'SchemaName' 
	AND TABLE_NAME = 'TableName'
	AND IS_NULLABLE = 'YES'
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 03/08/2013 :  12:43:30  Show Profile  Reply with Quote
JimF ...Thanks.....This should produce a table named Null in the tempdb directory, but I'm not seeing it?

Also I cant run it twice:

Server: Msg 2714, Level 16, State 6, Line 2
There is already an object named '#Nulls' in the database.

Bryan Holmstrom
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 03/08/2013 :  12:47:07  Show Profile  Reply with Quote
You only need to create the table once, then
select * from #nulls (in the same session you created the table)

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
75 Posts

Posted - 03/08/2013 :  14:05:07  Show Profile  Reply with Quote
Thanks, the table is being created. Now the problem is the "table" name is invalid.

When is try and opoen it I get this error:

dbo.#null__________________________________________________00000009D328

Invalid object name

Any ideas, seems the dbo object name is too long or something.


Bryan Holmstrom
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000