| Author |
 Topic  |
|
|
bholmstrom
Starting Member
USA
32 Posts |
 Posted - 03/08/2013 : 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
Flowing Fount of Yak Knowledge
3831 Posts |
Posted - 03/08/2013 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 03/08/2013 : 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
Flowing Fount of Yak Knowledge
3831 Posts |
Posted - 03/08/2013 : 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
Starting Member
USA
32 Posts |
Posted - 03/08/2013 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 03/08/2013 : 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
Starting Member
USA
32 Posts |
Posted - 03/08/2013 : 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 |
|
|
| |
Topic |
|
Populate a Query with a list of Tables
