CODEWITHSUNDEEP
sql-serverssms
hanesjw
hanesjw

Reputation: 2574

Is there a way to open a sql file in SQL Server Management Studio?

I have thousands of stored procedures in my data base. And I can't stand scrolling through the entire list to find the SPROC I'm looking for. Is there a command in sql server mgmt studio to open the file in the editor like 'OPEN dbo.SomeStoredProcedureName'

Upvotes: 1

Views: 1063

Answers (5)

Latish
Latish

Reputation: 126

SqlSmash lets you navigate easily to any object (including stored procedures) in SSMS.

Source

Disclaimer: I am the developer for the addin.

Upvotes: 0

Daniel Williams
Daniel Williams

Reputation: 9304

You could export the whole database to a creation script. Then it would be easy to search in your favorite file viewer.

From Management Studio you can right-click on the database, choose Tasks->Generate Scripts, and then select "Stored procedures". If you're just browsing, this is very handy. Also good to store such scripts in your code repository.

Upvotes: 0

amit_g
amit_g

Reputation: 31250

I don't believe there is such command but if you just want to see and not update you could use 

sp_helptext 'dbo.SomeStoredProcedureName'

This messes up the formatting so you shouldn't use it to update (while you can). I also map a key to it Ctrl-F1 that can be used to just write the name and select an d press Ctrl-F1.

enter image description here

Upvotes: 1

paulsm4
paulsm4

Reputation: 121609

You've got lots of options, depending on exactly what you're trying to do:

  • You can "open file" and graphically browse to the directory you want, then graphically scroll down to and open the file you want.

  • You can "use" the database you want, and "exec" the stored procedure you want from a command-line query window.

  • You can "exec sp_helptext XXX" the stored procedure to see the text in a command-line query window.

  • You can use "filter" in the GUI to eliminate stuff you don't want to see

  • You can write a VBScript or Powershell script to do the same stuff the SSMS GUI lets you do.

  • Etc etc

Upvotes: 0

Yuck
Yuck

Reputation: 50825

There is no T-SQL command to do this, as SSMS is just a client management tool. Your best bet is to use the Filter tool built in to SSMS:

enter image description here

enter image description here

Upvotes: 3

Related Questions