Find NULLs in ALL databases - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Find NULLs in ALL databases

Author

Topic

Sparxx
Starting Member

23 Posts

Posted - 2010-02-11 : 12:28:00

I apologize if this has already been posted, but I was not able to find a post relating to my specific need.

Is there some sort of combine or inner join script that I can run using a hybrid of

select * from [DatabaseName1].dbo.[TableName1]
where [ColumnName1] IS NULL

...so that I don't have to run 1 script at a time per column in my databases?

Currently our database infrastructure contains 8 actively accessed databases, each of the 8 has at least 4-50 tables, and within each of those anywhere between 2-150 columns. Even if I have to reduce down to running a grouped script 8 times (once per database), that would be acceptable too.

I had tried putting in wildcards of !, * and % in place of the [ColumnName] to possibly bring up all columns that contain a NULL under a specific Table; and have also tried wildcards in the [TableName] to try and detect all Tables AND Columns with NULLs in them. But that may be too complicated with both being wildcarded.

Any thoughts? I will try and provide any additional information I can, or have access to.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 12:30:17

for this you need to loop over each and every column in your table and check i guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 12:54:22

When I have to do things like this I mechanically generate the SQL to do the query - so as not to have to type it several times.

Thus:


select * 
from [DatabaseName1].dbo.[TableName1]
where [ColumnName1] IS NULL

becomes:


SELECT	'SELECT ''' + COLUMN_NAME + '.' + TABLE_NAME + ''' AS TheColumn, *
FROM	[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']
WHERE	[' + COLUMN_NAME + '] IS NULL
' + 'GO'
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	DATA_TYPE IN ('nchar', 'char', 'nvarchar', 'varchar', 'ntext', 'text')
	AND IS_NULLABLE = 'YES'
ORDER BY TABLE_NAME, ORDINAL_POSITION

You'll have to do more work if you want it to work for any database, I only have a couple of minutes spare

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 01:12:11

See if you can make use of this
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx

Madhivanan

Failing to plan is Planning to fail

Sparxx
Starting Member

23 Posts

Posted - 2010-02-12 : 09:43:33

I really like that script, Kristen!

Right now, I am running it on our company's test environment which as MORE than the 8 standard databases that our software uses and this script finds the NULLs in EVERY database that exists.

Is there a way to point this 'generation' script to compile queries against those 8 specific databases? I am playing around with it to see if I can, but if you can offer insight, I will be in your debt!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 09:48:40

quote:
Originally posted by Sparxx

I really like that script, Kristen!

Right now, I am running it on our company's test environment which as MORE than the 8 standard databases that our software uses and this script finds the NULLs in EVERY database that exists.

Is there a way to point this 'generation' script to compile queries against those 8 specific databases? I am playing around with it to see if I can, but if you can offer insight, I will be in your debt!

its possible. do you have that list of 8 dbs stored in some table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 09:58:27

quote:
Originally posted by Sparxx

I really like that script, Kristen!

Right now, I am running it on our company's test environment which as MORE than the 8 standard databases that our software uses and this script finds the NULLs in EVERY database that exists.

Is there a way to point this 'generation' script to compile queries against those 8 specific databases? I am playing around with it to see if I can, but if you can offer insight, I will be in your debt!

Just run my solution against 8 databases

Madhivanan

Failing to plan is Planning to fail

Sparxx
Starting Member

23 Posts

Posted - 2010-02-12 : 10:03:48

To quote Carlos Mencia - DEE DUH DEE!!!

I just need to use the USE [DatabaseName] command before firing the script off.
This may be the ticket! (at least I hope so)

One final question, though. How do I NOT have 'GO' show up at the end of each generated query? As of right now, I am using the Replace All option to get rid of the 'GO' at the end of each line

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 10:04:26

If it is 8 DBs (i.e. "not many") then I would run it manually in each DB.

Running it for all DBs requires using dynamic SQL, that needs doubling-up-the-already-doubled-up-single-quotes

and it all gets a bit hairy at that point.

Sparxx
Starting Member

23 Posts

Posted - 2010-02-12 : 10:08:45

In our Test System, the database names are as follows:

EDIT_XP_CODETABLES
EDIT_XP_DASHBOARD
EDIT_XP_DOCMASTER
EDIT_XP_HISTORY
EDIT_XP_REMIT
EDIT_XP_SETTINGS
EDIT_XP_TRACKING
EDIT_XP_TRANS

For our live systems and our client's systems, 'EDIT' would be replaced with something descriptive to the live system or of the client.

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 10:11:34

" How do I NOT have 'GO' show up at the end of each generated query? As of right now, I am using the Replace All option to get rid of the 'GO' at the end of each line"

Remove the


 + 'GO'

bit. I put it in because parsing a huge script make take ages, and the GO's will prevent that. Won't make any difference to how it runs, will it?

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 10:13:53

I normally do that my having

USE MyDatabasePrefix_XP_CODETABLES

(where "MyDatabasePrefix_" is stored just like that, and is designed to be absolutely unique) then I can do a find & replace on "MyDatabasePrefix_" for "EDIT" or the Client's Name equivalent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 10:17:34

or use system sp sp_Msforeachdb and restrict it to those in your list of 8 dbs.


exec sp_MSforeachdb ' if ''?'' IN (''your db1'',''yourdb2'',.....,''yourdb8'') 
begin 
<current code>
end'

http://www.databasejournal.com/features/mssql/article.php/1490641/SPMSFOREACHDB---Databases-List-Via-TSQL.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources