Script to "find" table...
-
Hello
I'm looking for a way to write a script that will run against a SQL Server's databases, and look for a certain table in each database. Is there any way to accomplish this?
-
It's not pretty, but see whether this helps:
declare @sql varchar(400)
set @sql = 'select * from ?.information_schema.tables where table_name = ''[tablename]'''
exec sp_msforeachdb @sql
--replace [tablename] with the name of the table you are searching for
The output can be tidied up fairly simply through the use of a temp table.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil, that worked GREAT! Thank you very much. 1/2 my goal is worked out.
I now need to take each db indentified as possesing the "looked for table", and do a dump (xml, flat, whatever) of everything in it, minus data. This would include sp's, triggers, tables, jobs, etc..
Any ideas?! BTW, thanks again!
-
Glad I could help.
I imagine you're after a SQL script that will recreate the database (minus the data). The way that I would do this is in Enterprise Manager. Right click a database, All Tasks, Generate SQL Script. Click on Show All and select Script All Objects.
This will not script the jobs, which are part of a system database (msdb I think) - but you can script jobs separately just by right-clicking them in EM ... as above.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here's EXACTLY what I'm after..my company puts out a product which adds a database, or databases, to someone's SQL server. We want a script we can give them via email/ftp that will
A. Hunt through their server and look for a specific table in all their databases. (done)
B. For each database the above (A) returns, the script dumps all info about EACH database into a text file. We'd like to be able to recreate their environment if needed.
THat's what I'm looking to do. Part A is done, thanks to you!
-
Hi!
You have to write a program using DMO.
If you do not want to write - look here: http://www.sqlservercentral.com/scripts/contributions/1151.asp
It's your part B and even more.
Good Luck!
-
>We'd like to be able to recreate their environment if needed.
See "Backups"?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Also be aware that the information_schema.* views only return results within the context of the executing user.
ie - if you're logged on as (any) user, and there is a table which this user has no permissions on, information_schema.* views will not return any information about that object. (though it does still exists)
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply