Populate a Query with a list of Tables

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)

Populate a Query with a list of Tables

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-08 : 10:11:15

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-08 : 12:02:12

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?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-08 : 12:06:00

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-08 : 12:08:30

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'

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-08 : 12:43:30

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-08 : 12:47:07

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

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-08 : 14:05:07

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources