Sql snippets file for SQL ops studio.

sql.json

{
    // Place your snippets for sql here. Each snippet is defined under a snippet name and has a prefix, body and 
    // description. The prefix is what is used to trigger the snippet and the body will be expanded and inserted. Possible variables are:
    // $1, $2 for tab stops, $0 for the final cursor position, and ${1:label}, ${2:another} for placeholders. Placeholders with the 
    // same ids are connected.
    // Example:
    // "Print to console": {
    // 	"prefix": "log",
    // 	"body": [
    // 		"console.log('$1');",
    // 		"$2"
    // 	],
    // 	"description": "Log output to console"
    // }

    "select top 100 rows": {
        "prefix": "st100",
        "body": "SELECT TOP 100 * FROM ${1:schemaName}.${2:tableName}",
        "description": "Select the top 100 rows"
    },
    "select * from fragment": {
        "prefix": "ssf",
        "body": [
            "SELECT ${3:columns}",
            "FROM ${1:schema}.${2:tableName}",
            "$4"
        ],
        "description": "Select * from fragment"
    },
    "group by": {
        "prefix": "gb",
        "body": "GROUP BY $1",
        "description": "Group by fragment"
    },
    "order by": {
        "prefix": "ob",
        "body": "ORDER BY $1",
        "description": "Order by fragment"
    },
    "select count from": {
        "prefix": "scf",
        "body": "SELECT COUNT(*) FROM $1",
        "description": "Count number of records returned by query"
    },
    "create database": {
        "prefix": "cdb",
        "body": [
            "CREATE DATABASE $database_name",
            "ON",
            "PRIMARY ( -- or use FILEGROUP filegroup_name",
            "\tNAME = $database_name_data,",
            "\tFILENAME = '$database_name.mdf'",
            ") --, and repeat as required",
            "LOG ON",
            "(",
            "\tNAME = $database_name_log,",
            "\tFILENAME = '$database_name.ldf'",
            ") --, and repeat as required",
            "--COLLATE collation_name",
            "--WITH",
            "--  DB_CHAINING ON/OFF",
            "--  TRUSTWORTHY ON/OFF",
            "--FOR LOAD",
            "--FOR ATTACH",
            "--WITH",
            "--  ENABLE_BROKER",
            "--  NEW_BROKER",
            "--  ERROR_BROKER_CONVERSATIONS",
            "--FOR ATTACH_REBUILD_LOG",
            "GO"
        ]
    },
    "create stored procedure": {
        "prefix": "cp",
        "body": [
            "-- Create a new stored procedure called '${1:procName}' in schema '${2:schemaName}",
            "--SET QUOTED_IDENTIFIER ON|OFF",
            "--SET ANSI_NULLS ON|OFF",
            "--GO",
            "CREATE PROCEDURE $2.$1",
            "    @${3:parameter_name} AS INT",
            "-- WITH ENCRYPTION, RECOMPILE, EXECUTE AS CALLER|SELF|OWNER| 'user_name'",
            "AS",
            "    ${4:proc_body}",
            "GO",
            "--SET QUOTED_IDENTIFIER ON|OFF",
            "--SET ANSI_NULLS ON|OFF",
            "--GO"
        ],
        "description": "Create stored procedure"
    },
    "create table": {
        "prefix": "ct",
        "body": [
            "-- Create a new table called '${1:TableName}' in schema '${2:SchemaName}'",
            "-- Drop the table if it already exists",
            "IF OBJECT_ID('$2.$1', 'U') IS NOT NULL",
            "DROP TABLE $2.$1",
            "GO",
            "-- Create the table in the specified schema",
            "CREATE TABLE $2.$1",
            "(",
            "   $1Id INT NOT NULL PRIMARY KEY, -- primary key column",
            "   Column1 [NVARCHAR](50) NOT NULL,",
            "   Column2 [NVARCHAR](50) NOT NULL",
            "   -- specify more columns here",
            ");",
            "GO"
        ],
        "description": "Create table"
    },
    "create view": {
        "prefix": "cv",
        "body": [
            "CREATE VIEW $view_name$",
            "--WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA",
            "AS",
            "\t$SELECTEDTEXT$$CURSOR$",
            "-- WITH CHECK OPTION",
            "GO"
        ],
        "description": "Create view"
    },
    "drop database": {
        "prefix": "dd",
        "body": "DROP DATABASE $database_name",
        "description": "Drop database"
    },
    "insert into": {
        "prefix": "ii",
        "body": "INSERT INTO $schema.$tableName",
        "description": "Insert into fragment"
    },
    "join": {
        "prefix": "j",
        "body": "JOIN ",
        "description": "Join fragment"
    }
}