Azure Data Studio – My SQL snippets freebie #18

I showed how easy it is to create a snippet in a previous blog and said I would share my snippets file. These are the current SQL snippets

sqlblocking – List blocked processes order by head blocking processes

sqlschema – List the schema in the current database

sqlGetIndexStats – Lists the current index statistics, read, writes, seeks, scans, etc.

sqlGetTableSizes – Show the table sizes and row sizes

sqlcolumnListSameLine -Generate a list of columns comma-separated on the same line, in column order

sqlcolumnListOrderbyName Generate list of columns comma separated by name

sqldatabasesizes – list of data and log sizes and combined free space

sqlfindcolumns List columns in all tables whose name is like ‘TableName’

sqljobsteps –  All the jobs enabled, ordered by step_id and also a blank line in for readability!

sqlstartstep – Code to start a job at a specific step

sqltablesizes – list current database sizes and rows

sqlWhoisactive – Adam Mechanic Whoisactive but with a twist ( its a temporary procedure so can use on servers that don’t have it)

If you want to try these yourself rather than link each file you can download my sql.json file.

Browse to your settings folder replacing the your login bit. backup your sql.json and copy in the one from above.

C:\Users\yourlogin\AppData\Roaming\azuredatastudio\User

Go into Azure Data Studio and type in sql…. and one of the selections above, feel free to use to your heart’s content.

I will maintain the settings.json from time to time but if you have any you think would be useful feel free to contact me and I’ll add them in.

Posted in Azure Data Studio, Productivity, Snippets, TSQL, Uncategorized | Leave a comment

Azure Data Studio Snippets #17

I know snippets have been around for years and I have utilised lots of SSMS tools like ssmstoolspack back in the day persuading a company I worked for to buy a copy for the developers.  Of late my SSMS tool of choice is ssmsboost, even buying it my self personally, well my company which is more or less the same, they also have a free community version for people involved in the community and they have helped out with Sponsorship several times for Manchester SQL Saturday and Manchester DataPlatform events.

I never really bought into snippets however till I started using Azure Data Studio and wow, so much productivity.

The first thing is to install the Query Editor Boost extension which has several features but the one this post is about is to easily add in snippets

09 Snippets Nothing better than showing by example in several easy steps. This came from trying to write a query without knowing the schema in the database, I know I’ll create a snippet.

1) Ctrl-N for a new SQL file

2) Paste in the code you want to make a snippet of

SELECT name
FROM sys.schemas
WHERE name NOT IN (
        ‘db_owner’
        , ‘db_accessadmin’
        , ‘db_securityadmin’
        , ‘db_ddladmin’
        , ‘db_backupoperator’
        , ‘db_datareader’
        , ‘db_datawriter’
        , ‘db_denydatareader’
        , ‘db_denydatawriter’
        )
ORDER BY 1
3) Open up the menu Ctrl-Shift+P and type in QE and choose Save New Query Template
13 New snippet
4) Enter a Name for the snippet
14 sqlSchema
5) Enter a prefix – I tend to keep the same name it’s easier
15 sqlSchema
6) Give the snippet a description
16 schema description
Now when you type sql you get a list of snippets and the description
17 snippets in action
hitting return you get the SQL script pasted in.
These are all stored in a text file in a JSON format in the following location, so you can build a library and take it with you.
C:\Users[userName]\AppData\Roaming\azuredatastudio\User\snippets
I am now going through a process of adding in my useful scripts into snippets. It takes literally seconds once you have the SQL.
I’ll add in a post with a link to my snippets file and the code snippets I have.
Posted in Azure Data Studio, Azure Data Studio Extensions, Management Studio, Productivity, Snippets, Uncategorized | Leave a comment

Azure Data Studio – Poor SQL Formatting extension #16

The stock formatting can be improved on with the Poor SQL formatting extension first it needs installing once installed open settings (Ctrl+,) type in poor, hit enter

11 poor formating

Configure as required to your specific configuration, at least change the keywords to UPPER case! You can assign a short cut to format the SQL or use the menu, (Ctrl-Shift+P) and type in poor, or if you have just used it will be at the top of the list.

12 Poor formating menu

Top tip don’t set as short cut key Shift +I as I did, and only realised the issue when capitalising the letter I and wondered why all the text went funny!

It’s so easy to just make your SQL look nice now 😉

Posted in Azure Data Studio, Azure Data Studio Extensions, Configuration | Leave a comment

AzureDataStudio Blog posts contents page

AzureDataStudio Blog series

1) Azure Data Studio and SQL Saturdays
2) ADS Keyboard Short cuts – part 1
3) ADS Keyboard Shortcut and Name drop
4) ADS Server and Switching Profiles   – Setting up ability to switch in different dashboards, user profiles
5) Azure Data Studio – Database Status Dashboards – Steps to create a dashboard and clever trick to run a script across different SQL versions with different syntax
6) ADS Extensions – Brief intro to extensions
7) SQL Saturday Belgium – My 1st SQL European SQL Saturday experience
8) ADS Error switching in PowerShell– with switching to PowerShell Kernel in Notebooks and fix
9) Azure Data Studio Notebooks Markup– Annotating notebooks, headers, lists, and formatting, with a clever hack to get around text alignment issues
10)  Azure Data Studio – wow more time saving – Searching for servers or adding in new servers
11) Leeds and Manchester Data Platform Meetup AzureDataStudio – Trip over the Pennines and another great feature to compare clipboard and the active window
12) Azure Data Studio -The gift that keeps on giving – Even easier filtering for files
13) Less AzureDataStudio more Yorkshire– Trip to Haworth
14) Getting the git repository- AzureDataStudio. And an offer that I might regret this one! Maybe not  – link to the repository
15) This contents page.
16) Azure Studio formatting TSQL – easy formatting of SQL text
17) Azure Data Studio Snippets
18) Azure Data Studio my snippets – list of TSQL files and link to the snippets sql.json file
19) Azure Data Studio Notebook Charts – charting in Notebooks
20) Azure Data Studio – Working remotely, setting up ADS to run without installation
21) Azure Data Studio – Run book– How I utilised Notebooks for a SQL upgrade/migration
22) Azure Data Studio Data Scripter– Ability to script tables to insert statements and specify where clauses
23) Azure Data Studio– More short cut keys – duplicating lines, compare clipboard to the active window
24)
25)
26)
27)
28)
29)
30)
31)

Posted in Azure Data Studio, Azure Data Studio Extensions, Dataplatform, Docker, Markup, SQL Notebooks | Leave a comment

Getting the git repository- AzureDataStudio. And an offer that I might regret this one! Maybe not (14/31)

I have stored the GitHub Presentation in the following location

https://github.com/martincroft/SQLPresentations.git

So_how_difficult_can_it_be

I quite like presenting the same talk at multiple locations (turns out they have been cancelled since I drafted this), but we need to look after each other so that’s cool as well.

I’ve had a weekend off and been away, it’s crazy how difficult this is to blog daily and the guys who do it all the time are amazing,

I’ve still managed it but its not what I wanted it to be. I’ve learned a lot and hopefully shared a lot because that’s what it was really about

I’ve come up with a crazy idea as I sit in a hotel in Bronte country (and had a few drinks most of the afternoon) lets find someone to share anything I know. I’m not biging myself up, but I’d like to think I’ve got something to share and maybe someone is interested who knows 🤔

Posted in Azure Data Studio, Productivity | Leave a comment

Less AzureDataStudio more Yorkshire (13/31)

Its day 13 and Friday funny that, as probably explains what I’m about to get to shortly. I’m on a weekend break with the wife in the lovely North Yorkshire village of Haworth.

I’ve just checked in at the sleeping house at the bottom of the famous cobbled high street. Below is the view from the hotel.

Just switched the laptop on for todays blog to discover I have little charge and no charger. So any posts will need to be done from my phone.

Azuredata studio thoughts of the day, the previous two nights were presenting at two different usergroups and if was interesting to see how many people havent used AzureDataStudio but hopefully a few will now have a play.

Last night in Leeds and previous night in Manchester few interesting questions came out, one around can you get dashboards to auto refresh, which would make them quite a useful feature. So far tried to find a short cut key for refresh with out much luck and fudging the sql script called to have a loop and a wait for but to no avail,but it’s interesting the ideas people are coming up with to customise the ADS experience.

Also the hotel room toilet seems to be twinned with a toilet in Africa

,whatever next

Posted in Azure Data Studio, Yorkshire | Leave a comment

Azure Data Studio -The gift that keeps on giving (day 12/31)

I thought I had come up with a clever way of searching in Azure Data Studio to search for the filename of a SQL or PowerShell file using a PowerShell script that read the file in reversed the file, and added the file name to the end of the file and reserved it back so basically adding the name of the file to the top with a comment like:-

#My SQL Script.sql #SQL

I can now search on the file name then I discovered if type Ctrl+P and just type the name, I can find the file name with no clever Powershell shenanigans.

I love this app. I am still finding stuff out using day today.

Then a couple of days ago I discovered if you start typing in the pane with all your scripts it can filter on what you type, the example below I type CMD and find all files with CMD in the title. I knew it did this in the PowerShell section but hadn’t realised it did it here!

search

I had started to think blogging 31 times in a month would be a challenge and I imagine it will be, but I keep finding out new features.

This blog was finished off on the way to Leeds Dataplatform with @Ianpike both of doing techie stuff ( he’s finishing his presentation just saying)

Posted in Azure Data Studio, Tips & Tricks | Tagged , | Leave a comment