Solved

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

Posted on 2014-03-30
9
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 69

Accepted Solution

by:
Qlemo earned 500 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 69

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with MySQL query - graph 3 26
SQL Distinct Question 3 15
SQL profiler 3 18
TSQL: return only records which have only one type of value. 1 14
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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