• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

Search SSMS script for references to any tables in the database.

I hope this is an easy one. I can execute the procedure sp_tables on a database and get a list of all possible tables that could be used in a script. I want to take this list and compare it against a SQL script that has 5000+ lines so that I can essentially change the references in the script from

TableName to  

[database].[dbo].[TableName] or other combinations

I really need to do it just like this. There may be other ways to accomplish "the same thing" but I really want to do this particular thing. List A has keywords List B has text be searched on.

I am not adverse to buying a power editor. I already have Ultraedit so that or free would be preferred. If you know of a product don't just point me to a link. Show me how it works or where to find the information to make it work.

Thanks
0
mike1142
Asked:
mike1142
1 Solution
 
David ToddSenior DBACommented:
Hi,

SSMS has a find and replace function that is under ctrl+H or the edit menu (Edit | Fund and Replace | Quick Replace)

Now you may be wanting to do something a bit more complex than that, but taking what you said and face value, you can do a search and replace over a selection, open document, all open documents, etc ) and replace tablename1 with somedatabase.dbo.tablename1

HTH
  David
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
mike1142Author Commented:
No, not even close. I know search and replace even across files. The problem is that the sp I am looking to edit is written for its dbo. I want to execute it in another db so I need to change references from 'TableName' or 'dbo.TableName' to a fully qualified table name.

Thanks
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Are you open to PowerShell scripts?
Do you really enforce having to change the SP? There are means to execute a stored procedure within a different DB or schema.
0
 
mike1142Author Commented:
I am looking for portability. I work with this vendors database product with many customers and the database name is slightly different at some and I want an easy way to convert from one to the other. Once the script(s) are changed to use fully qualified a simple search and replace is possible.

The scripts as well as enhancements and modifications use a separate database as best practice to keep the original db structure intact. So read-only to the main database and queries from the custom.

There may be another way and I am interested but this is not the only use case. There is also a "missing table" scenario and a "must have these tables" scenario. which are similar.
0
 
jogosCommented:
A change all is always tricky, which order you want to change? What if there is an Order-table do you concider variations like dbo.order, dbo.[order], [dbo].[order] or will you also change every string 'order'.  You say table names but are views, procedures or UDF also possible?
Combinations are infinite if you ask me.

A safer way depending on in which circumstances you want to use this
a)
Concider the sql cmd utility and using a variable (commandline or :setvar ) to inject the variable part in your sql.   Then make a script that prefixes every object-reference with the db-reference
http://technet.microsoft.com/en-us/library/ms188714.aspx

b) Work with the content of sys;sql_modules
You can get the procedure definition of the reference-db and create it in the other database so everey reference will be to objects in the same database. The execution will need rights to create/delete procedures in the  db's you want to query.

An example on how it is used just to query for strings in a procedure
http://sqlserverquestions.mssqltips.com/8704/to-know-the-procedures-in-which-insert-into-tablename-which-is-used/
0
 
mike1142Author Commented:
http://tangodude.wordpress.com/2013/04/15/powershell-multiple-find-replace-in-files-with-lookup-list/

Thanks for the point in the right direction did not thinking of powerscript. Did exactly what I wanted.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Great find, and exactly what I had in mind ;-).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now