?
Solved

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

Posted on 2014-03-30
9
Medium Priority
?
388 Views
Last Modified: 2014-03-31
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
Comment
Question by:mike1142
8 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39965887
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39966186
0
 

Author Comment

by:mike1142
ID: 39966701
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 39966830
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
 

Author Comment

by:mike1142
ID: 39966864
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
 
LVL 25

Expert Comment

by:jogos
ID: 39967565
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
 

Author Closing Comment

by:mike1142
ID: 39967916
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 39967935
Great find, and exactly what I had in mind ;-).
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question