The SQL Server management tools offer a lot of hidden features, but finding all of these features is sometimes done by accident or by necessity to get a particular job done.
One particular feature that would be helpful is to assign shortcuts for particular stored procedures that you run quite frequently. By default SQL Server offers default shortcuts such as Alt+F1 (sp_help), Ctrl+1 (sp_who) and Ctrl+2 (sp_lock). How can you assign your own frequently used stored procedures to shortcut keys?
Click to options of SQL Server management tools
This tutorial I will show you how to assign customer store procedure to Ctrl + 3 | Ctrl + 4
On your SQL Server, click to master database -> choose Stored Procedures -> run below procedure
sp_dumpparam
or download link: http://www.mediafire.com/file/p7bzmdrldcxiyz1/sq_dumpparam.sql/file
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_dumpparam] Script Date: 07/06/2018 15:59:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_dumpparam] (@SUName AS varchar(8000),@DumpValue AS int=0)
AS
/*
----- #DUMPPARAM VERSION# 18.05.06
----- Purpose: Noi hai cot IsNull va Default
----- 2008/30/07: xuat ra unicode
*/
DECLARE @Obj_ID AS int
DECLARE @Error AS varchar(100)
DECLARE @Code AS nvarchar(4000)
DECLARE @Line AS nvarchar(4000)
DECLARE @DefinedLength AS int
DECLARE @BlankSpaceAdded AS int
DECLARE @TextLength AS int
DECLARE @AddOnLen AS int
DECLARE @Param AS varchar(8000)
DECLARE @LFCR AS varchar(2) -- Line feed and return character
DECLARE @Pos AS int
DECLARE @BasePos AS int
DECLARE @SpacePos AS int
DECLARE @CodePos AS int -- Position of main code
DECLARE @ParamValueBasePos AS int -- Position of param value
DECLARE @ParamValuePos AS int -- Position of param value
DECLARE @CommentPos AS int -- comment text pos
DECLARE @CommentValue AS varchar(200) -- comment text
DECLARE @ParamName AS varchar(200)
DECLARE @ParamValue AS varchar(200)
DECLARE @DeclareVar AS varchar(1000)
DECLARE @ParamLine AS varchar(8000)
DECLARE @ObjectName AS varchar(200)
DECLARE @MaxCharShow AS varchar(20)
DECLARE @CountComma AS int
DECLARE @CommaPos AS int
DECLARE @LineCount AS int
DECLARE @ObjectType AS int
DECLARE @ShowCodeOnly AS int
DECLARE @TypeName AS varchar(100)
DECLARE @DateTimeConvertValue AS DateTime
DECLARE @DebugProcedureName AS varchar(100)
DECLARE @ProcType AS int
DECLARE @ViewType AS int
DECLARE @FunctionType AS int
DECLARE @TableType AS int
DECLARE @InTempDB AS int
DECLARE @TriggerType AS int
DECLARE @strSQL AS nvarchar(4000)
SET NOCOUNT ON
SET @ProcType=0
SET @ViewType=1
SET @FunctionType=2
SET @TableType=3
SET @TriggerType=4
SET @LFCR=CHAR(13)+CHAR(10)
SET @DefinedLength=4000
SET @SUName=LTRIM(RTRIM(@SUName))
SET @SpacePos=CHARINDEX(' ',@SUName+' ')
SET @ParamLine=LTRIM(SUBSTRING(@SUName,@SpacePos,LEN(@SUName)))
SET @ParamLine=REPLACE(@ParamLine,CHAR(145),CHAR(39))
SET @ParamLine=REPLACE(@ParamLine,CHAR(146),CHAR(39))
SET @ObjectName=SUBSTRING(@SUName,1,@SpacePos)
SET @InTempDB= CASE WHEN LEFT(@ObjectName,1)='#' THEN 1 ELSE 0 END -- In temporary database ?
IF @InTempDB=0
SET @Obj_ID=OBJECT_ID(@ObjectName)
ELSE
SET @Obj_ID=OBJECT_ID('TEMPDB..'+@ObjectName)
SET @DebugProcedureName='DEBUG_TEST'
IF @Obj_ID IS NULL
BEGIN
SET @Error='Invalid object name '''+@ObjectName+''' in database '''+CASE WHEN @InTempDB=0 THEN DB_NAME() ELSE 'TEMPDB' END+''''
RAISERROR(@Error,0,1)
RETURN
END
IF @InTempDB=0
SELECT TOP 1 @ObjectType=(CASE WHEN xtype ='P' THEN @ProcType
WHEN xtype ='V' THEN @ViewType
WHEN xtype='U' OR xtype='S' THEN @TableType
WHEN xtype='TR' THEN @TriggerType
ELSE @FunctionType END) FROM SYSOBJECTS WHERE ID=@Obj_ID AND xtype IN ('FN', 'IF', 'TF','P','V','U','S','TR')
ELSE
SELECT TOP 1 @ObjectType=(CASE WHEN xtype ='P' THEN @ProcType
WHEN xtype ='V' THEN @ViewType
WHEN xtype='U' OR xtype='S' THEN @TableType
WHEN xtype='TR' THEN @TriggerType
ELSE @FunctionType END) FROM TEMPDB..SYSOBJECTS WHERE ID=@Obj_ID AND xtype IN ('FN', 'IF', 'TF','P','V','U','S','TR')
IF @ObjectType IS NULL
BEGIN
SET @Error=''''+@ObjectName+''' is not an FUNCTION/VIEW/STORE PROCEDURE/TRIGGER or TABLE!'
RAISERROR(@Error,0,1)
RETURN
END
IF @ObjectType=@TableType
BEGIN
DECLARE @TempName AS varchar(100)
DECLARE @TabColumn AS varchar(200),
@TabType AS varchar(20),
@TabDefaultValue AS varchar(1000),
@TabDescription AS nvarchar(3000)
IF @InTempDB=0
DECLARE TableInfo_Cur CURSOR LOCAL FOR
SELECT COL.Name AS [Column Name],
DATA_TYPE + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL
THEN CASE WHEN DATA_TYPE = 'DECIMAL'
THEN '('+convert(nvarchar(10),NUMERIC_PRECISION)+','+convert(nvarchar(10),NUMERIC_SCALE)+')' ELSE ''
END
ELSE '('+ convert(nvarchar(20),CHARACTER_MAXIMUM_LENGTH) +')'
END AS [Data Type],
(CASE WHEN IsNullAble = 0 THEN '' ELSE 'NULL' END)+(CASE WHEN IsNull(COM.Text, '') = '' THEN '' ELSE (CASE WHEN IsNullAble=1 THEN ',' ELSE '' END) +SUBSTRING(COM.Text,2,LEN(COM.Text)-2) END) AS [Default],
IsNull(CAST(SP.value AS nvarchar(3000)), '') as [Description]
FROM SYSCOLUMNS COL
INNER JOIN SYSOBJECTS TAB ON COL.ID = TAB.ID
INNER JOIN INFORMATION_SCHEMA.COLUMNS DATACOLUMNS ON DATACOLUMNS.table_name = TAB.name AND DATACOLUMNS.column_name = COL.name
LEFT JOIN SYSOBJECTS TAB2 ON TAB2.ID = COL.CDefault
LEFT JOIN SYSCOMMENTS COM ON COM.ID = TAB2.ID
LEFT JOIN sys.extended_properties SP ON SP.name = 'MS_Description'
AND SP.major_id = TAB.id AND SP.minor_id = COL.colid
WHERE TAB.ID=@Obj_ID
ORDER BY COL.ColOrder
ELSE
DECLARE TableInfo_Cur CURSOR LOCAL FOR
SELECT COL.Name AS [Column Name],
(CASE WHEN TYP.Name like '%char%' THEN TYP.Name +' (' + LTRIM(RTRIM(STR(COL.Length))) + ')'
WHEN TYP.Name ='decimal' THEN TYP.Name +'(' + LTRIM(RTRIM(STR(COL.XPrec))) + ', ' + LTRIM(RTRIM(STR(COL.XScale))) + ')'
ELSE TYP.Name END) AS [Data Type],
(CASE WHEN IsNullAble = 0 THEN '' ELSE 'NULL' END)+(CASE WHEN IsNull(COM.Text, '') = '' THEN '' ELSE (CASE WHEN IsNullAble=1 THEN ',' ELSE '' END) +SUBSTRING(COM.Text,2,LEN(COM.Text)-2) END) AS [Default],
IsNull(CAST(SP.value AS nvarchar(3000)), '') as [Description]
FROM TEMPDB..SYSCOLUMNS COL
INNER JOIN TEMPDB..SYSOBJECTS TAB ON COL.ID = TAB.ID
INNER JOIN TEMPDB..SYSTYPES TYP ON TYP.XType = COL.XType AND TYP.XUSERTYPE<>256
LEFT JOIN TEMPDB..SYSOBJECTS TAB2 ON TAB2.ID = COL.CDefault
LEFT JOIN TEMPDB..SYSCOMMENTS COM ON COM.ID = TAB2.ID
LEFT JOIN sys.extended_properties SP ON SP.name = 'MS_Description'
AND SP.major_id = TAB.id AND SP.minor_id = COL.colid
WHERE TAB.ID=@Obj_ID
ORDER BY COL.ColOrder
SET @MaxCharShow=4000
SET @TempName='##'+SUBSTRING(CAST (CONVERT(DECIMAL(17,17),RAND()) AS CHAR(19)),3,19) -- Not duplicated name
SET @strSQL='CREATE TABLE '+@TempName+' ([Column Name] varchar(100),[Description] nvarchar(3000),[Data Type] varchar(100),[Default] varchar(100),
[First Value] nvarchar('+@MaxCharShow+'),[Second Value] nvarchar('+@MaxCharShow+'),[Third Value] nvarchar('+@MaxCharShow+'))'
EXEC(@strSQL) -- Create temp table
IF @InTempDB=1 SET @ObjectName = 'TEMPDB..'+@ObjectName
OPEN TableInfo_Cur
FETCH NEXT FROM TableInfo_Cur INTO @TabColumn,@TabType,@TabDefaultValue,@TabDescription
WHILE @@FETCH_STATUS=0
BEGIN
IF UPPER(@TabColumn) IN ('SELECT') OR @TabColumn LIKE '[0-9#$^&*-+|!@]%'
SET @Tabcolumn='['+@Tabcolumn+']'
SET @strSQL='DECLARE @Value01 AS nvarchar('+@MaxCharShow+'),@Value02 AS nvarchar('+@MaxCharShow+'),@Value03 AS nvarchar('+@MaxCharShow+')'+@LFCR
SET @strSQL=@strSQL+CASE WHEN @TabType<>'image' THEN
'SELECT TOP 1 @Value01=CONVERT (NVARCHAR('+@MaxCharShow+'),'+@TabColumn+(CASE WHEN CHARINDEX('DATETIME',@TabType)=1 THEN ',121' ELSE '' END)+') FROM '+@ObjectName+'
IF @@ROWCOUNT<>1' ELSE '' END++@LFCR
SET @strSQL=@strSQL+'
SET @Value01=''?'''+@LFCR
SET @strSQL=@strSQL+CASE WHEN @TabType<>'image' THEN
'SELECT TOP 2 @Value02=CONVERT (NVARCHAR('+@MaxCharShow+'),'+@TabColumn+(CASE WHEN CHARINDEX('DATETIME',@TabType)=1 THEN ',121' ELSE '' END)+') FROM '+@ObjectName+'
IF @@ROWCOUNT<>2' ELSE '' END+@LFCR
SET @strSQL=@strSQL+'
SET @Value02=''?'''+@LFCR
SET @strSQL=@strSQL+CASE WHEN @TabType<>'image' THEN
'SELECT TOP 1 @Value03=CONVERT (NVARCHAR('+@MaxCharShow+'),'+@TabColumn+(CASE WHEN CHARINDEX('DATETIME',@TabType)=1 THEN ',121' ELSE '' END)+') FROM '+@ObjectName+'
IF @@ROWCOUNT<>3' ELSE '' END+@LFCR
SET @strSQL=@strSQL+'
SET @Value03=''?'''+@LFCR
SET @strSQL=@strSQL+'
INSERT INTO '+@TempName+' ([Column Name],[Description],[Data Type],[Default],[First Value],[Second Value],[Third Value])
VALUES ('''+@TabColumn+''',N'''+@TabDescription+''','''+@TabType+''','''+REPLACE(@TabDefaultValue,'''', '''''')+''',@Value01,@Value02,@Value03)'
EXEC(@strSQL)
FETCH NEXT FROM TableInfo_Cur INTO @TabColumn,@TabType,@TabDefaultValue,@TabDescription
END
EXECUTE('SELECT * FROM '+@TempName)
EXEC('DROP TABLE '+@TempName)
RETURN
END
SET @TypeName=(CASE @ObjectType WHEN @ProcType THEN 'Procedure' WHEN @ViewType THEN 'View' WHEN @FunctionType THEN 'Function' WHEN @TriggerType THEN 'Trigger' END)
SET @ShowCodeOnly=(CASE WHEN (@ParamLine='' AND @DumpValue=0) OR @ObjectType=@ViewType THEN 1 ELSE 0 END) -- Chi xem code, khong tu dong xu ly code
-- Build code into lines
CREATE TABLE #CodeLine -- line by line
(LineId int ,Text nvarchar(4000) collate database_default)
DECLARE Code_Cur CURSOR LOCAL FOR
SELECT text FROM SYSCOMMENTS WHERE ID=@Obj_ID AND Encrypted=0
ORDER BY Number,Colid FOR READ ONLY
OPEN Code_Cur
SET @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
trailing blank spaces*/
SET @LineCount=0
FETCH NEXT FROM Code_Cur INTO @Code
WHILE @@FETCH_STATUS >= 0
BEGIN
SET @BasePos = 1
SET @Pos = 1
SET @TextLength = LEN(@Code)
WHILE @Pos != 0
BEGIN
--Looking for end of line followed by carriage return
SET @Pos = CHARINDEX(@LFCR, @Code, @BasePos)
--If carriage return found
IF @Pos != 0
BEGIN
/*If new value for @Lines length will be > then the
**set length then insert current contents of @line
**and proceed.
*/
WHILE (IsNull(LEN(@Line),0) + @BlankSpaceAdded + @Pos-@BasePos + LEN(@LFCR)) > @DefinedLength
BEGIN
SET @AddOnLen = @DefinedLength-(IsNull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CodeLine VALUES
( @LineCount,IsNull(@Line, '') + IsNull(SUBSTRING(@Code, @BasePos, @AddOnLen), ''))
SELECT @Line = NULL, @LineCount = @LineCount + 1,@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SET @Line = IsNull(@Line, '') + IsNull(SUBSTRING(@Code, @BasePos, @Pos-@BasePos + LEN(@LFCR)), '')
SET @BasePos = @Pos+2
INSERT #CodeLine VALUES( @LineCount, @Line )
SET @LineCount = @LineCount + 1
SET @Line = NULL
END
ELSE
--else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
/*If new value for @Lines length will be > then the
**defined length
*/
WHILE (IsNull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SET @AddOnLen = @DefinedLength - (IsNull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CodeLine VALUES
( @LineCount,
IsNull(@Line, '') + IsNull(SUBSTRING(@Code, @BasePos, @AddOnLen), ''))
SELECT @Line = NULL, @LineCount = @LineCount + 1,@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SET @Line = IsNull(@Line, '') + IsNull(SUBSTRING(@Code, @BasePos, @TextLength-@BasePos+1 ), '')
IF LEN(@Line) < @DefinedLength and charindex(' ', @Code, @TextLength+1 ) > 0
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
END
END
END
FETCH NEXT FROM Code_Cur into @Code
END
IF @Line is NOT NULL
INSERT #CodeLine VALUES( @LineCount, @Line )
IF @LineCount=0 -- no line return
BEGIN
SET @Error=@TypeName+' '''+@ObjectName+''' has contains no line!'
RAISERROR(@Error,0,1)
END
-- Out for process
DECLARE Code_Line CURSOR LOCAL FOR
SELECT text FROM #CodeLine ORDER BY LineID
FOR READ ONLY
OPEN Code_Line
IF @DumpValue=2 -- Create procedure for debugger
BEGIN
SET @strSQL='IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N''[DBO].['+@DebugProcedureName+']'') AND '
SET @strSQL= @strSQL+'OBJECTPROPERTY(ID, N''IsProcedure'') = 1)' -- Is Procedure
SET @strSQL=@strSQL+@LFCR+'DROP PROCEDURE [DBO].['+@DebugProcedureName+']'
SET @strSQL=@strSQL+@LFCR+'GO'
SET @strSQL=@strSQL+@LFCR+'SET QUOTED_IDENTIFIER ON'
SET @strSQL=@strSQL+@LFCR+'GO'
SET @strSQL=@strSQL+@LFCR+'SET ANSI_NULLS ON'
SET @strSQL=@strSQL+@LFCR+'GO'+@LFCR+@LFCR
SET @strSQL=@strSQL+@LFCR+'---------------- SP_Dumpparam procedure generator for DEBUG'
SET @strSQL=@strSQL+@LFCR+'CREATE PROCEDURE ['+@DebugProcedureName+']'
SET @strSQL=@strSQL+@LFCR+'AS'
PRINT @strSQL
END
IF @ShowCodeOnly=1
BEGIN
SET @strSQL='IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N''[DBO].['+@ObjectName+']'') AND '
IF @ObjectType=@ProcType
SET @strSQL= @strSQL+'OBJECTPROPERTY(ID, N''IsProcedure'') = 1)' -- Is Procedure
IF @ObjectType=@ViewType
SET @strSQL=@strSQL+'OBJECTPROPERTY(ID, N''IsView'') = 1)' -- Is View
IF @ObjectType=@FunctionType
SET @strSQL=@strSQL+'XTYPE IN (N''FN'', N''IF'', N''TF''))' -- Is Function
IF @ObjectType=@TriggerType
SET @strSQL=@strSQL+'OBJECTPROPERTY(ID, N''IsTrigger'') = 1)' -- Is Trigger
SET @strSQL=@strSQL+@LFCR+'DROP '+UPPER(@TypeName)+' [DBO].['+@ObjectName+']'
SET @strSQL=@strSQL+@LFCR+'GO'
SET @strSQL=@strSQL+@LFCR+'SET QUOTED_IDENTIFIER ON'
SET @strSQL=@strSQL+@LFCR+'GO'
SET @strSQL=@strSQL+@LFCR+'SET ANSI_NULLS ON'
SET @strSQL=@strSQL+@LFCR+'GO'
IF @ObjectType IN (@ProcType,@ViewType,@FunctionType,@TriggerType)
PRINT @strSQL
GOTO lbShowCodeOnly --- Ignore all when show code only
END
FETCH NEXT FROM Code_Line INTO @Code
WHILE (@@Fetch_Status=0) -- Find first param macth, it means after define procedure line
BEGIN
SET @Code=REPLACE(@Code,CHAR(9),' ') -- convert tab to space
SET @BasePos=CHARINDEX('CREATE ' ,@Code) -- Defintion
IF @BasePos>0
BEGIN
IF CHARINDEX(@TypeName,LTRIM(SUBSTRING(@Code,@BasePos+LEN('CREATE '),LEN(@Code)))) =1 -- Is first definition ?
BREAK
END
FETCH NEXT FROM Code_Line INTO @Code
END
-- Process to find params
CREATE TABLE #Param -- params table
(DeclareVar varchar(1000),Name varchar(200),Value varchar(1000),Comment varchar(200) COLLATE database_default)
SET @CodePos=0
SET @ParamValueBasePos=0
WHILE (@@Fetch_Status=0)
BEGIN
SET @Code=REPLACE(@Code,CHAR(9),' ') -- convert tab to space
SET @CommentPos=CHARINDEX('--',@Code,@BasePos) -- comment
IF @CommentPos>0
SET @Code=LEFT(@Code, @CommentPos-1)
SET @Pos=CHARINDEX('@',@Code,@BasePos)
WHILE @Pos>0
BEGIN
SET @CodePos=CHARINDEX('AS',@Code,@BasePos) -- Is code position ?
SET @SpacePos=CHARINDEX(' ',@Code+' ',@Pos)
IF (@CodePos=0) OR (@CodePos>@Pos)
BEGIN
SET @ParamValuePos=CHARINDEX(',',@ParamLine+',',@ParamValueBasePos)
SET @ParamName=LTRIM(RTRIM(SUBSTRING(@Code,@Pos,@SpacePos-@Pos)))
SET @BasePos=CHARINDEX(',',@Code,@SpacePos)
---- Test comma in string ?
SET @CountComma=0
SET @CommaPos=CHARINDEX('''',@ParamLine,@ParamValueBasePos)
lbTestComma:
WHILE @CommaPos<@ParamValuePos AND @CommaPos>0
BEGIN
SET @CountComma=@CountComma+1
SET @CommaPos=CHARINDEX('''',@ParamLine,@CommaPos+1)
END
IF @CountComma%2>0 -- comma in string, find next position
BEGIN
SET @ParamValuePos=CHARINDEX(',',@ParamLine+',',@ParamValuePos+1)
GOTO lbTestComma -- continue for testing
END
IF @ParamValuePos>0
BEGIN
SET @CommentPos=CHARINDEX('--',@Code,@Pos)
SET @DeclareVar=RTRIM(SUBSTRING(@Code,@Pos,(CASE WHEN @BasePos=0 THEN
(CASE WHEN @CommentPos>0 AND @CommentPos<@ParamValuePos THEN @CommentPos ELSE LEN(@Code)+1 END)
ELSE @BasePos END)-@Pos))
SET @ParamValue=SUBSTRING(@ParamLine,@ParamValueBasePos,@ParamValuePos-@ParamValueBasePos)
IF CHARINDEX(' DATETIME',@DeclareVar)>0 AND LTRIM(RTRIM(@ParamValue))<>'NULL'-- Date time value ?
BEGIN
SET @ParamValue=CONVERT(VARCHAR(50),CAST(REPLACE(@ParamValue,'''','') AS DATETIME),121)
IF CHARINDEX('00:00:00.000',@ParamValue)>0 -- only date
BEGIN
SET @ParamValue=LEFT(@ParamValue, CHARINDEX('00:00:00.000',@ParamValue)-1)
END
SET @ParamValue='CAST('''+@ParamValue+''' AS DATETIME)'
END
IF CHARINDEX(' VARCHAR',@DeclareVar)>0 OR CHARINDEX(' CHAR',@DeclareVar)>0
BEGIN
IF CHARINDEX('''',@ParamValue)=0 AND UPPER(RTRIM(LTRIM(@ParamValue)))<>'NULL'
SET @ParamValue=''''+@ParamValue+''''
END
SET @ParamValueBasePos=@ParamValuePos+1
SET @DeclareVar=RTRIM(REPLACE(@DeclareVar,@LFCR,'')) -- Remove caterine return line feed code
IF RIGHT(REPLACE(@DeclareVar,' ',''),2)='))' -- Last param and end with ) ?
SET @DeclareVar=LEFT(@DeclareVar,LEN(@DeclareVar)-1)
INSERT INTO #Param(DeclareVar,Name,Value) VALUES (@DeclareVar,@ParamName,@ParamValue)
END
ELSE
BEGIN -- param not enought ?
SET @Error=''''+@ObjectName+''' expects parameter '+@ParamName+', which was not supplied.'
RAISERROR(@Error,0,1)
RETURN
END
IF @BasePos=0 -- last param ?
GOTO lbParamProcess
ELSE
SET @Pos=CHARINDEX('@',@Code,@BasePos)
END
ELSE
GOTO lbParamProcess
END
SET @BasePos=0
FETCH NEXT FROM Code_Line INTO @Code
END
lbParamProcess:
-- Out for view
DECLARE Param_Line CURSOR LOCAL SCROLL FOR
SELECT DeclareVar,Name,Value FROM #Param
FOR READ ONLY
OPEN Param_Line
IF @DumpValue=0
PRINT '/* ------------------------------------------------------------------------------------------------------------------------------------'
FETCH NEXT FROM Param_Line INTO @DeclareVar,@ParamName,@ParamValue
WHILE (@@Fetch_Status=0)
BEGIN
IF LEN(@DeclareVar)+LEN(@ParamName)+LEN(@ParamValue)>80 -- Line too long
BEGIN
PRINT 'DECLARE '+@DeclareVar
PRINT ' SET '+@ParamName+'='+@ParamValue
END
ELSE
PRINT 'DECLARE '+@DeclareVar+' SET '+@ParamName+'='+@ParamValue
FETCH NEXT FROM Param_Line INTO @DeclareVar,@ParamName,@ParamValue
END
IF @DumpValue=0
PRINT ' ------------------------------------------------------------------------------------------------------------------------------------*/'
-- Is definition line of procedure ?
FETCH FROM Code_Line INTO @Code
WHILE (@@Fetch_Status=0) AND CHARINDEX('AS',LTRIM(@Code))<>1
BEGIN
FETCH NEXT FROM Code_Line INTO @Code
END
lbShowCodeOnly: --- Ignore all for show code
FETCH NEXT FROM Code_Line INTO @Code -- Ignore definition of procedure
WHILE (@@Fetch_Status=0)
BEGIN
IF @DumpValue=0 AND @ShowCodeOnly=0
BEGIN
FETCH FIRST FROM Param_Line INTO @DeclareVar,@ParamName,@ParamValue ----Fill param with param value
WHILE (@@Fetch_Status=0) -- process line by line
BEGIN
SET @BasePos=0
SET @Pos=CHARINDEX(@ParamName,@Code,@BasePos) -- Find param in code
WHILE (@Pos>0) -- Replace all param with param value
BEGIN
IF CHARINDEX(SUBSTRING(@Code+' ',@Pos+LEN(@ParamName),1) ,' ,=()<>+-* /%#@|^'+CHAR(9)+CHAR(10)+CHAR(13))>0 -- Param must be seperated by ,=()<>+-*/(TAB)(CRLF)
BEGIN
SET @Code=STUFF (@Code,@Pos,LEN(@ParamName),@ParamValue) -- Replace
SET @BasePos=@Pos+LEN(@ParamValue)
END
ELSE
SET @BasePos=@Pos+1 -- next value
SET @Pos=CHARINDEX(@ParamName,@Code,@BasePos)
END
FETCH NEXT FROM Param_Line INTO @DeclareVar,@ParamName,@ParamValue ----continue next line
END
END
PRINT @Code -- Show code in Result Text Window
FETCH NEXT FROM Code_Line INTO @Code
END
IF @ShowCodeOnly=1 -- End declare
BEGIN
SET @strSQL='GO'
SET @strSQL=@strSQL+@LFCR+'SET QUOTED_IDENTIFIER OFF'
SET @strSQL=@strSQL+@LFCR+'GO'
SET @strSQL=@strSQL+@LFCR+'SET ANSI_NULLS ON'
SET @strSQL=@strSQL+@LFCR+'GO'
IF @ObjectType IN (@ProcType,@ViewType,@FunctionType,@TriggerType)
BEGIN
PRINT @strSQL
PRINT 'GRANT '+CASE WHEN @ObjectType=@ProcType THEN 'EXECUTE' ELSE 'SELECT' END+' ON '+@SUName+ ' TO AgentMSFE, AgentMSBE'
END
END
sp_selectall
or download link: http://www.mediafire.com/file/whf1eqo275qd8s2/sp_selectall.sql/file
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_selectall] Script Date: 07/06/2018 15:58:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_selectall] (@TableName As varchar(8000))
AS
--print @TableName
DECLARE @index int, @tmpTableName As varchar(8000), @Where As varchar(8000)
SET @tmpTableName = LTRIM(RTRIM(@TableName))
SET @index = CHARINDEX (CHAR(13) + CHAR(10),@TableName)
IF @index > 1
BEGIN
SET @TableName = SUBSTRING (@tmpTableName, 1, @index - 1)
SET @Where = RIGHT(@tmpTableName, LEN(@tmpTableName) - @index - 1)
END
ELSE
BEGIN
SET @TableName = @tmpTableName
SET @Where = ''
END
IF @Where <> ''
SET @Where = ' WHERE ' + @Where
--SELECT @TableName, @Where
--return 0
IF EXISTS (SELECT * FROM syscolumns col inner join sysobjects tab ON col.id=tab.id WHERE tab.name=@TableName and Col.name=@TableName+'_id')
EXEC ('SELECT * FROM '+@TableName + @Where + ' ORDER BY '+ @TableName+'_id DESC')
ELSE
EXEC ('SELECT * FROM '+@TableName + @Where )
We’re using ctrl+3: select all table
[How to use]: type the table you want to view all data, remember choose correct database, double click into Table name on SQLQuery.sql => press CTRL + 3, the result on below table
[How to use]: type the table you want to view dumpparam data, remember choose correct database, double click into Table name on SQLQuery.sql => press CTRL + 4, the result on below table
If you have any feedback leave your comment, we can discuss about it!,
Thanks
Zidane