CODEWITHSUNDEEP
sql-servert-sql
Sulaiman
Sulaiman

Reputation: 35

How to store queries in SQL Server database?

I manage a mini database and I write procedures for complex transactions and data cleansing. I also do a lot of ad-hoc querying and I save all of my queries in a folder. Is there any way I can save these queries in the database so that some of my peers can review my SQL queries?

In my search, I understand that I can write a procedure for smaller queries too. But I want to know if there is another method to do this?

Upvotes: 1

Views: 7244

Answers (3)

JMS
JMS

Reputation: 1

You can create a new folder in the Template Browser and add code in new templates. If you want to share these ACROSS your team using SSMS, you can also do the following: You DO have to store the code elsewhere, but it can be accessed within SSMS by all users when set up this way on their machines:

See: https://www.sqlservercentral.com/articles/ssms-shared-sql-templates

Short synopsis: Store code examples in central location and re-point SQL templates folder on each user machine to the central location, by using mklink to create new link to SQL folder under the following location so that the SQL folder will no longer point to it, but to the alternate central location path specified:

C:\Users\YourUserName\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates

To do this open the command prompt and:

  • go to user path above and rename SQL folder found under there: ren Sql Sql_Old

  • create symbolic link: mklink /D Sql C:\ss\Internal\Code\TSQL\SSMS_Templates

  • if successful, you will see:

    symbolic link created for Sql <<===>> path of central code

Afterwards, the template browser will link to the central location and show whatever is in there.

Upvotes: 0

Brad
Brad

Reputation: 12255

You can have queries (views) persisted in the database itself. You can use the CREATE statement to create views, stored procedures, table value functions etc. which will be accessible through intelisense and show up in the database object tree

enter image description here

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19194

For a select statement use a view:

CREATE VIEW MyView 
AS
SELECT Columns FROM TABLE

Now you can select from that 

SELECT * FROM MyView

and join to it:

SELECT * FROM MyView
INNER JOIN SomethingElse 
ON MyView.ID = SomethingElse.ID

For scripts that update/delete/insert or do procedural things in order, use a stored procedure instead.

Upvotes: 1

Related Questions