Show Queries

Question

Show Queries

water490

SSC Eights!

Points: 918
More actions
November 15, 2021 at 1:20 am

#3952972

Hi everyone
I am moving from Access to SQL Server so I am learning more about SQL Server. In Access I can view the objects for my project (such as tables and queries). Is there a way to show the queries I have written in SQL Server so it shows up in the Object Explorer (the same way tables show up)? Not sure if this is important or not. I saved the queries in a different folder from the default one.
Thank you

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 1

No. Not queries in the object explorer. If they're stored procedures, view, or functions, then you'll see them in the object explorer.

Maybe somehow in a "project" but I don't "projects" and so I'm ill equipped to advise there. I'm not even sure if it's possible or not. I kind of doubt it, though I could be wrong there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398716 More actions · Answer 2

Welcome to the party.

Pretty much what Jeff says (btw, that's a good mantra around here).

SQL Server's principal tool, SQL Server Management Studio (SSMS), has a thing called a project. I don't recommend using it. At all. However, if you go to the menu and select File - New - Project... you'll get a window that will let you set up a way to manage queries. Know up front, it sucks. It's not going to make you happy. It's not like Access. But, it's there and I wouldn't try to hide it from you.

Instead, I'd suggest using version control. Create a Github repository (or another one). Keep your queries there. In fact, keeping your entire databases in version control is a very good idea. There are 3rd party tools that can help with that, but you can do it all manually too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeffrey Williams SSC Guru Points: 90260 More actions · Answer 3

If all you want is the ability to lookup and access your queries - then you might consider Azure Data Studio instead of SSMS. That does allow you to define a folder and access the scripts/code in that folder - as well as some other features that might be more useful.

Either way - if you setup a folder structure that makes sense for you, then either SSMS or ADS can navigate the folders and give you easy access to your scripts.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 4

Jeffrey Williams wrote:

If all you want is the ability to lookup and access your queries - then you might consider Azure Data Studio instead of SSMS. That does allow you to define a folder and access the scripts/code in that folder - as well as some other features that might be more useful.
Either way - if you setup a folder structure that makes sense for you, then either SSMS or ADS can navigate the folders and give you easy access to your scripts.

In SSMS, that would be only during a FILE OPEN, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

water490 SSC Eights! Points: 918 More actions · Answer 5

Thank you everyone. I have been saving the files to a folder. it works for me because I only have a few queries. I am so used to Access so I have to learn the SQL Server way of doing things 🙂

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 6

water490 wrote:

Thank you everyone. I have been saving the files to a folder. it works for me because I only have a few queries. I am so used to Access so I have to learn the SQL Server way of doing things 🙂

If that folder were to suddenly disappear, what would you do other than soil your britches?

If you make stored procedures, views, and functions, they'll get backed up with the rest of the database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

water490 SSC Eights! Points: 918 More actions · Answer 7

I am super new to SQL Server. I downloaded it on the weekend and transferred over my Access queries.

You bring up an excellent point. I will take a look at these ideas and implement them. Thank you so much for bringing this to my attention.

Grant Fritchey SSC Guru Points: 398716 More actions · Answer 8

Jeff Moden wrote:

water490 wrote:
Thank you everyone. I have been saving the files to a folder. it works for me because I only have a few queries. I am so used to Access so I have to learn the SQL Server way of doing things 🙂
If that folder were to suddenly disappear, what would you do other than soil your britches?
If you make stored procedures, views, and functions, they'll get backed up with the rest of the database.

And, hence, version control.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 9

Grant Fritchey wrote:

Jeff Moden wrote:
water490 wrote:
Thank you everyone. I have been saving the files to a folder. it works for me because I only have a few queries. I am so used to Access so I have to learn the SQL Server way of doing things 🙂
If that folder were to suddenly disappear, what would you do other than soil your britches?
If you make stored procedures, views, and functions, they'll get backed up with the rest of the database.
And, hence, version control.

... on a system that actually gets backed up. 😀 I've seen people lose a disk on their version control systems and not have a backup. It made for a great shoe filling moment. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ratbak Ten Centuries Points: 1335 More actions · Answer 10

If you are running certain queries frequently, turn them into stored procedures. Then they will be saved in SQL Server and accessible through the object explorer. And stored procedures tend to make us think more about our queries and use parameters to encapsulate the variations in filters/scenarios. With ad-hoc queries, there can be a temptation to just keep hacking the script as we go to accomodate one-offs.

aaron.reese SSChampion Points: 13498 More actions · Answer 11

It's a conceptual thing. in SQL server a query is an ad-hoc thing that you write in Management Studio (or Azure Data Studio, or VS Code). Access doesn't have ad-hoc queries, you have to store them in the application. In SQL these would be views and are persisted to the database. If you need to combine many views it may be easier to store them as stored procedures.

A view can only return one results set and cannot persist data. A stored procedure can return more than one results set (but not reccomended) and can persist data.