Searching for a field type - 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

General SQL Server Forums

New to SQL Server Programming

Searching for a field type

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-07-04 : 06:27:32

Hi all

I need to check all the fields in all tables in all databases to check if any are nvarchar.

Is there any "reasonably" quick way of doing this without checking each table in each database by hand?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-04 : 09:32:32

You can use the sp_MSforeachdb stored proc. It is an undocumented stored proc, but everyone seems to use it without a second thought. You would do something like this, for example:

exec sp_MSforeachdb 
'select 
	''?'' AS [Database],
	[COLUMN_NAME],
	[CHARACTER_MAXIMUM_LENGTH]
 from 
	[?].[Information_schema].[columns]
 where
	[DATA_TYPE] = ''nvarchar''';

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-07-04 : 10:12:49

Thanks for that.
Is it possible to add in the table name as well?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-04 : 10:21:39

quote:
Originally posted by rmg1

Thanks for that.
Is it possible to add in the table name as well?

You can add TABLE_NAME column (and any other column returned by the INFORMATION_SCHEMA.COLUMNS catalog view).

exec sp_MSforeachdb 
'select 
	''?'' AS [Database],
	[COLUMN_NAME],
	[CHARACTER_MAXIMUM_LENGTH],
	[TABLE_NAME]
 from 
	[?].[Information_schema].[columns]
 where
	[DATA_TYPE] = ''nvarchar''';

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-07-04 : 10:48:51

Thanks for that, it'll save ma about 2 hours searching.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources