TSQL -Custom StoredProcedures.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "code",
            "source": [
                "-- Start a SQL Agent Job\r\n",
                "CREATE PROCEDURE [dbo].[spStartAgentJob]\r\n",
                "\t   @Jobname VARCHAR(180)\r\n",
                "\t\tWITH EXECUTE AS 'Domain\\Username'  -- <- Must Change\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "\r\n",
                "BEGIN TRY\r\n",
                "Exec('msdb.dbo.sp_start_job  @job_name = '+ @Jobname)\r\n",
                "Select  'Running Job: ' + @Jobname as [Status]\r\n",
                "END TRY\r\n",
                "BEGIN CATCH\r\n",
                "Select  ERROR_MESSAGE()  as [Status]\r\n",
                "END CATCH\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "b0ca6760-675a-4e06-915f-13a20ed5edff",
                "language": "sql",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "-- Run a file SSIS Package\r\n",
                "\r\n",
                "EXEC sp_configure 'show advanced options', 1\r\n",
                "GO\r\n",
                "-- To update the currently configured value for advanced options.\r\n",
                "RECONFIGURE\r\n",
                "GO\r\n",
                "-- To enable the feature.\r\n",
                "EXEC sp_configure 'xp_cmdshell', 1\r\n",
                "GO\r\n",
                "-- To update the currently configured value for this feature.\r\n",
                "RECONFIGURE\r\n",
                "GO　\r\n",
                "\r\n",
                "CREATE PROCEDURE [dbo].[sp_RunSSIS64]\r\n",
                "\t   @pFile NVARCHAR(400),\r\n",
                "       @pPass NVARCHAR(25)\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "SET @pFile  = 'call \"C:\\Program Files\\Microsoft SQL Server\\150\\DTS\\Binn\\dtexec.exe\" /file \"' + @pFile + '\" /de ' + @pPass \r\n",
                "EXEC master..xp_cmdshell @pFile\r\n",
                "END\r\n",
                "\r\n",
                "/*\r\n",
                "-- DISABLE XP_CMDSHELL - \r\n",
                "-- Disallow further advanced options to be changed.\r\n",
                "EXEC sp_configure 'show advanced options', 0\r\n",
                "GO\r\n",
                "-- Update the currently configured value for advanced options.\r\n",
                "RECONFIGURE\r\n",
                "GO\r\n",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "1b3781c1-f566-40a5-a1f2-09122d6370f3",
                "language": "sql",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "-- Run a Powershell file\r\n",
                "EXEC sp_configure 'show advanced options', 1\r\n",
                "GO\r\n",
                "-- To update the currently configured value for advanced options.\r\n",
                "RECONFIGURE\r\n",
                "GO\r\n",
                "-- To enable the feature.\r\n",
                "EXEC sp_configure 'xp_cmdshell', 1\r\n",
                "GO\r\n",
                "-- To update the currently configured value for this feature.\r\n",
                "RECONFIGURE\r\n",
                "GO　\r\n",
                "\r\n",
                "--create stored procedure　\r\n",
                "CREATE PROCEDURE spPowershell\r\n",
                " @pFile NVARCHAR(500)\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "SET @pFile  = 'powershell.exe -ExecutionPolicy Bypass -File \"' + @pFile + '\" '\r\n",
                "EXEC master..xp_cmdshell @pFile\r\n",
                "END\r\n",
                "\r\n",
                "/*\r\n",
                "-- DISABLE XP_CMDSHELL - \r\n",
                "-- Disallow further advanced options to be changed.\r\n",
                "EXEC sp_configure 'show advanced options', 0\r\n",
                "GO\r\n",
                "-- Update the currently configured value for advanced options.\r\n",
                "RECONFIGURE\r\n",
                "GO\r\n",
                "*/\r\n"
            ],
            "metadata": {
                "azdata_cell_guid": "4ad67d3b-92b0-4b78-95a5-46bc17c6ccbc",
                "language": "sql",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "-- To search all tables and columns for a given search string\r\n",
                "CREATE PROCEDURE [dbo].[spSearchAllTables]\r\n",
                "(\r\n",
                "\t@SearchStr nvarchar(100)\r\n",
                ")\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "\t-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.\r\n",
                "\r\n",
                "\tCREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))\r\n",
                "\r\n",
                "\tSET NOCOUNT ON\r\n",
                "\r\n",
                "\tDECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)\r\n",
                "\tSET  @TableName = ''\r\n",
                "\tSET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')\r\n",
                "\r\n",
                "\tWHILE @TableName IS NOT NULL\r\n",
                "\tBEGIN\r\n",
                "\t\tSET @ColumnName = ''\r\n",
                "\t\tSET @TableName = \r\n",
                "\t\t(\r\n",
                "\t\t\tSELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))\r\n",
                "\t\t\tFROM \tINFORMATION_SCHEMA.TABLES\r\n",
                "\t\t\tWHERE \t\tTABLE_TYPE = 'BASE TABLE'\r\n",
                "\t\t\t\tAND\tQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName\r\n",
                "\t\t\t\tAND\tOBJECTPROPERTY(\r\n",
                "\t\t\t\t\t\tOBJECT_ID(\r\n",
                "\t\t\t\t\t\t\tQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)\r\n",
                "\t\t\t\t\t\t\t ), 'IsMSShipped'\r\n",
                "\t\t\t\t\t\t       ) = 0\r\n",
                "\t\t)\r\n",
                "\r\n",
                "\t\tWHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)\r\n",
                "\t\tBEGIN\r\n",
                "\t\t\tSET @ColumnName =\r\n",
                "\t\t\t(\r\n",
                "\t\t\t\tSELECT MIN(QUOTENAME(COLUMN_NAME))\r\n",
                "\t\t\t\tFROM \tINFORMATION_SCHEMA.COLUMNS\r\n",
                "\t\t\t\tWHERE \t\tTABLE_SCHEMA\t= PARSENAME(@TableName, 2)\r\n",
                "\t\t\t\t\tAND\tTABLE_NAME\t= PARSENAME(@TableName, 1)\r\n",
                "\t\t\t\t\tAND\tDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')\r\n",
                "\t\t\t\t\tAND\tQUOTENAME(COLUMN_NAME) > @ColumnName\r\n",
                "\t\t\t)\r\n",
                "\t\r\n",
                "\t\t\tIF @ColumnName IS NOT NULL\r\n",
                "\t\t\tBEGIN\r\n",
                "\t\t\t\tINSERT INTO #Results\r\n",
                "\t\t\t\tEXEC\r\n",
                "\t\t\t\t(\r\n",
                "\t\t\t\t\t'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) \r\n",
                "\t\t\t\t\tFROM ' + @TableName + ' (NOLOCK) ' +\r\n",
                "\t\t\t\t\t' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2\r\n",
                "\t\t\t\t)\r\n",
                "\t\t\tEND\r\n",
                "\t\tEND\t\r\n",
                "\tEND\r\n",
                "\r\n",
                "\tSELECT ColumnName, ColumnValue FROM #Results\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "3a31f1a0-fb69-45d0-a3b7-f2d41e32a1d0",
                "language": "sql",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "-- To search all columns in a table for a given search string\r\n",
                "CREATE PROCEDURE [dbo].[spSearchAllInTable]\r\n",
                "(\r\n",
                "\t@SearchStr nvarchar(100),\r\n",
                "\t@TableName nvarchar(256)\r\n",
                ")\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "\r\n",
                "\t-- Org Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.\r\n",
                "\r\n",
                "\tCREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))\r\n",
                "\r\n",
                "\tSET NOCOUNT ON\r\n",
                "\r\n",
                "\tDECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)\r\n",
                "\tSET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')\r\n",
                "\r\n",
                "\tBEGIN\r\n",
                "\t\tSET @ColumnName = ''\r\n",
                "\r\n",
                "\t\tWHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)\r\n",
                "\t\tBEGIN\r\n",
                "\t\t\tSET @ColumnName =\r\n",
                "\t\t\t(\r\n",
                "\t\t\t\tSELECT MIN(QUOTENAME(COLUMN_NAME))\r\n",
                "\t\t\t\tFROM \tINFORMATION_SCHEMA.COLUMNS\r\n",
                "\t\t\t\tWHERE \t\tTABLE_SCHEMA\t= PARSENAME(@TableName, 2)\r\n",
                "\t\t\t\t\tAND\tTABLE_NAME\t= PARSENAME(@TableName, 1)\r\n",
                "\t\t\t\t\tAND\tDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')\r\n",
                "\t\t\t\t\tAND\tQUOTENAME(COLUMN_NAME) > @ColumnName\r\n",
                "\t\t\t)\r\n",
                "\t\r\n",
                "\t\t\tIF @ColumnName IS NOT NULL\r\n",
                "\t\t\tBEGIN\r\n",
                "\t\t\t\tINSERT INTO #Results\r\n",
                "\t\t\t\tEXEC\r\n",
                "\t\t\t\t(\r\n",
                "\t\t\t\t\t'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) \r\n",
                "\t\t\t\t\tFROM ' + @TableName + ' (NOLOCK) ' +\r\n",
                "\t\t\t\t\t' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2\r\n",
                "\t\t\t\t)\r\n",
                "\t\t\tEND\r\n",
                "\t\tEND\t\r\n",
                "END\r\n",
                "\r\n",
                "\tSELECT ColumnName, ColumnValue FROM #Results\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "0808e2a6-65bf-4219-a00c-e050b95575d3",
                "language": "sql",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "-- Issue GRANT EXEC statement for all stored procedures for a user\r\n",
                "CREATE PROCEDURE [dbo].[spGrantExecAllStoredProc] @user sysname AS\r\n",
                "SET NOCOUNT ON\r\n",
                "\r\n",
                "-- 1 - Variable declarations\r\n",
                "DECLARE @CMD1 varchar(8000)\r\n",
                "DECLARE @MAXOID int\r\n",
                "DECLARE @OwnerName varchar(128)\r\n",
                "DECLARE @ObjectName varchar(128)\r\n",
                "\r\n",
                "-- 2 - Create temporary table\r\n",
                "CREATE TABLE #StoredProcedures\r\n",
                "(OID int IDENTITY (1,1),\r\n",
                "StoredProcOwner varchar(128) NOT NULL,\r\n",
                "StoredProcName varchar(128) NOT NULL)\r\n",
                "\r\n",
                "-- 3 - Populate temporary table\r\n",
                "INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)\r\n",
                "SELECT ROUTINE_SCHEMA, ROUTINE_NAME\r\n",
                "FROM INFORMATION_SCHEMA.ROUTINES\r\n",
                "WHERE ROUTINE_NAME NOT LIKE 'dt_%'\r\n",
                "AND ROUTINE_TYPE = 'PROCEDURE'\r\n",
                "\r\n",
                "-- 4 - Capture the @MAXOID value\r\n",
                "SELECT @MAXOID = MAX(OID) FROM #StoredProcedures\r\n",
                "\r\n",
                "-- 5 - WHILE loop\r\n",
                "WHILE @MAXOID > 0\r\n",
                "BEGIN\r\n",
                "\r\n",
                "-- 6 - Initialize the variables\r\n",
                "SELECT @OwnerName = StoredProcOwner,\r\n",
                "@ObjectName = StoredProcName\r\n",
                "FROM #StoredProcedures\r\n",
                "WHERE OID = @MAXOID\r\n",
                "\r\n",
                "-- 7 - Build the string\r\n",
                "SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user\r\n",
                "--SELECT @CMD1 = 'GRANT EXEC ON [' + @OwnerName + '].[' + @ObjectName + '] TO [' + @user + ']'\r\n",
                "\r\n",
                "-- 8 - Execute the string\r\n",
                "-- SELECT @CMD1\r\n",
                "EXEC(@CMD1)\r\n",
                "\r\n",
                "-- 9 - Decrement @MAXOID\r\n",
                "SET @MAXOID = @MAXOID - 1\r\n",
                "END\r\n",
                "\r\n",
                "-- 10 - Drop the temporary table\r\n",
                "DROP TABLE #StoredProcedures\r\n",
                "\r\n",
                "SET NOCOUNT OFF"
            ],
            "metadata": {
                "azdata_cell_guid": "33bb6bde-0a55-4ceb-b924-1d22787948b6",
                "language": "sql",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "-- Check SQL Statement is vaild\r\n",
                "CREATE\tPROCEDURE [dbo].[spValidateSqlSyntax](\r\n",
                "@CustomQuery AS NVARCHAR(MAX),\r\n",
                "@StatusMsg AS VARCHAR(MAX) OUTPUT\r\n",
                ")\r\n",
                "AS\r\n",
                "BEGIN\r\n",
                "BEGIN TRY\r\n",
                "\r\n",
                "IF @CustomQuery IS NOT NULL BEGIN \r\n",
                "SET @CustomQuery =  'SET NOEXEC ON; ' + @CustomQuery + ' ; SET NOEXEC OFF;'\r\n",
                "\r\n",
                "END\r\n",
                "\r\n",
                "EXEC sp_executesql @CustomQuery\r\n",
                "SET @StatusMsg = 'Success'\r\n",
                "\r\n",
                "END TRY\r\n",
                "BEGIN CATCH\r\n",
                "SET @StatusMsg = ERROR_MESSAGE() \r\n",
                "END CATCH\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "aa7e71ea-83ba-46f4-af2b-996dea481cfa",
                "tags": [
                    "hide_input"
                ],
                "language": "sql"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "-- Write data to text file to a file destination. \r\n",
                "CREATE PROCEDURE spWriteStringToFile\r\n",
                " (\r\n",
                "@String Varchar(max), \r\n",
                "@Path VARCHAR(255),\r\n",
                "@Filename VARCHAR(100)\r\n",
                "\r\n",
                ")\r\n",
                "AS\r\n",
                "DECLARE  @objFileSystem int\r\n",
                "        ,@objTextStream int,\r\n",
                "        @objErrorObject int,\r\n",
                "        @strErrorMessage Varchar(1000),\r\n",
                "        @Command varchar(1000),\r\n",
                "        @hr int,\r\n",
                "        @fileAndPath varchar(80)\r\n",
                "\r\n",
                "set nocount on\r\n",
                "\r\n",
                "select @strErrorMessage='opening the File System Object'\r\n",
                "EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT\r\n",
                "\r\n",
                "Select @FileAndPath=@path+'\\'+@filename\r\n",
                "if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file \"'+@FileAndPath+'\"'\r\n",
                "if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile'\r\n",
                "    , @objTextStream OUT, @FileAndPath,2,True\r\n",
                "\r\n",
                "if @HR=0 Select @objErrorObject=@objTextStream, \r\n",
                "    @strErrorMessage='writing to the file \"'+@FileAndPath+'\"'\r\n",
                "if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String\r\n",
                "\r\n",
                "if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file \"'+@FileAndPath+'\"'\r\n",
                "if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'\r\n",
                "\r\n",
                "if @hr<>0\r\n",
                "    begin\r\n",
                "    Declare \r\n",
                "        @Source varchar(255),\r\n",
                "        @Description Varchar(255),\r\n",
                "        @Helpfile Varchar(255),\r\n",
                "        @HelpID int\r\n",
                "\r\n",
                "    EXECUTE sp_OAGetErrorInfo  @objErrorObject, \r\n",
                "        @source output,@Description output,@Helpfile output,@HelpID output\r\n",
                "    Select @strErrorMessage='Error whilst '\r\n",
                "            +coalesce(@strErrorMessage,'doing something')\r\n",
                "            +', '+coalesce(@Description,'')\r\n",
                "    raiserror (@strErrorMessage,16,1)\r\n",
                "    end\r\n",
                "EXECUTE  sp_OADestroy @objTextStream\r\n",
                "EXECUTE sp_OADestroy @objTextStream"
            ],
            "metadata": {
                "azdata_cell_guid": "73ee3b1f-a646-45dd-8624-fc164a7e4c2e",
                "language": "sql",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}