How can I determine if a SQL Server stored procedure parameter has a default?
Is there a way to determine programmatically if a SQL Server stored procedure parameter has a default? (Bonus poi开发者_如何学Gonts if you can determine what the default is.) SqlCommandBuilder.DeriveParameters() doesn't even try.
Thanks in advance for your help!
EDIT: I honestly don't care if it's a SQL Query, an SMO object, etc.
I found a way using SMO:
Server srv;
srv = new Server("ServerName");
Database db;
db = srv.Databases["MyDatabase"];
var Params = db.StoredProcedures["MyStoredProc"].Parameters;
foreach(StoredProcedureParameter param in Params) {
Console.WriteLine(param.Name + "-" + param.DefaultValue);
}
Not a big deal in SQL Server 2005 and up:
SELECT
pa.NAME,
t.name 'Type',
pa.max_length,
pa.has_default_value,
pa.default_value
FROM
sys.parameters pa
INNER JOIN
sys.procedures pr ON pa.object_id = pr.object_id
INNER JOIN
sys.types t ON pa.system_type_id = t.system_type_id
WHERE
pr.Name = 'YourStoredProcName'
Unfortunately, even though this seemed like a piece of cake - it doesn't work :-(
From Technet:
SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function.
So all you can do is either query sys.sql_modules or call SELECT object_definition(object_id) to basically get the SQL definition (the T-SQL source code) for your stored proc and then you'd need to parse that (sucks!! big time.....)
Seems like there's really no other way to do this ... I'm amazed and appaled.....
Maybe in SQL Server 2008 R2 ? :-) Marc
This is the SMO answer in PowerShell:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "MyServer\MyInstance"
$db = $srv.Databases["MyDatabase"];
$proc = $db.StoredProcedures["MyStoredProcedure"]
foreach($parameter in $proc.Parameters) {
if ($parameter.DefaultValue){
Write-Host "$proc , $parameter , $($parameter.DefaultValue)"
}
else{
Write-Host "$proc , $parameter , No Default Value"
}
}
Run the builtin sp_help stored procedure?
This is kind of a hack, but you could always just give optional parameters a special name like:
@AgeOptional = 15
...then write a simple method that checks a parameter to see if it's optional. Not ideal, but given the situation, it might actually be a decent solution.
This is what I did to get it. grab the section of the stored procedure starting with the first parameter up to the AS statement. Created a temporary stored procedure with the declare statements and returning union all of all the parameter ids, names, column types, if they have default, and their value. And then executed the stored procedure with assumption of if there is a equal sign between parameters they have default, and if they don't have default I passed null to the parameter during execution, and either read the resultset or if exists the stored procedure populated a temporary table so that I can query it later. I checked if there is any equal signs between parameters and if yes initially I assumed they have defaults. If there is a comment etc with equal sign the procedure that means they did not have default and during execution I did not pass any parameter, the execution failed, I caught the error message, read the parameter name and executed the procedure this time I passed null to the parameter. In the procedure I used a CLR string concat function, for that reason it won't compile if you execute directly, but you can probably replace with XML path or so, or email me back I can guide you through the clr if you want to. Since I did union all the parameters I casted them as varchar(max)
USE Util
GO
CREATE AGGREGATE [dbo].[StringConcat]
(@Value nvarchar(MAX), @Delimiter nvarchar(100))
RETURNS nvarchar(MAX)
EXTERNAL NAME [UtilClr].[UtilClr.Concat]
GO
CREATE FUNCTION dbo.GetColumnType (@TypeName SYSNAME,
@MaxLength SMALLINT,
@Precision TINYINT,
@Scale TINYINT,
@Collation SYSNAME,
@DBCollation SYSNAME)
RETURNS TABLE
AS
RETURN
SELECT CAST(CASE WHEN @TypeName IN ('char', 'varchar')
THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
ELSE CAST(@MaxLength AS VARCHAR)
END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation
ELSE ''
END
WHEN @TypeName IN ('nchar', 'nvarchar')
THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
ELSE CAST(@MaxLength / 2 AS VARCHAR)
END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation
ELSE ''
END
WHEN @TypeName IN ('binary', 'varbinary') THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
ELSE CAST(@MaxLength AS VARCHAR)
END + ')'
WHEN @TypeName IN ('bigint', 'int', 'smallint', 'tinyint') THEN @TypeName
WHEN @TypeName IN ('datetime2', 'time', 'datetimeoffset') THEN @TypeName + '(' + CAST (@Scale AS VARCHAR) + ')'
WHEN @TypeName IN ('numeric', 'decimal') THEN @TypeName + '(' + CAST(@Precision AS VARCHAR) + ', ' + CAST(@Scale AS VARCHAR) + ')'
ELSE @TypeName
END AS VARCHAR(256)) AS ColumnType
GO
go
USE [master]
GO
IF OBJECT_ID('dbo.sp_ParamDefault') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_ParamDefault AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_ParamDefault
@ProcName SYSNAME = NULL OUTPUT
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @SQL VARCHAR(MAX),
@ObjectId INT = OBJECT_ID(LTRIM(RTRIM(@ProcName))),
@FirstParam VARCHAR(256),
@LastParam VARCHAR(256),
@SelValues VARCHAR(MAX),
@ExecString VARCHAR(MAX),
@WhiteSpace VARCHAR(10) = '[' + CHAR(10) + CHAR(13) + CHAR(9) + CHAR(32) + ']',
@TableExists BIT = ABS(SIGN(ISNULL(OBJECT_ID('tempdb..#sp_ParamDefault'), 0))),
@DeclareSQL VARCHAR(MAX),
@ErrorId INT,
@ErrorStr VARCHAR(MAX)
IF @ObjectId IS NULL
BEGIN
SET @ProcName = NULL
PRINT '/* -- SILENCE OPERATION --
IF OBJECT_ID(''tempdb..#sp_ParamDefault'') IS NOT NULL DROP TABLE #sp_ParamDefault
CREATE TABLE #sp_ParamDefault (Id INT, NAME VARCHAR(256), TYPE VARCHAR(256), HasDefault BIT, IsOutput BIT, VALUE VARCHAR(MAX))
*/
EXEC dbo.sp_ParamDefault
@ProcName = NULL
'
RETURN
END
SELECT @SQL = definition,
@ProcName = QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectId)) + '.' + QUOTENAME(OBJECT_NAME(@ObjectId)),
@FirstParam = FirstParam,
@LastParam = LastParam
FROM sys.all_sql_modules m (NOLOCK)
CROSS APPLY (SELECT MAX(CASE WHEN p.parameter_id = 1 THEN p.name
END) AS FirstParam,
Util.dbo.StringConcat(p.name, '%') AS Params
FROM sys.parameters p (NOLOCK)
WHERE p.object_id = m.OBJECT_ID) p
CROSS APPLY (SELECT TOP 1
p.NAME AS LastParam
FROM sys.parameters p (NOLOCK)
WHERE p.object_id = m.OBJECT_ID
ORDER BY parameter_id DESC) l
WHERE m.object_id = @ObjectId
IF @FirstParam IS NULL
BEGIN
IF @TableExists = 0
SELECT CAST(NULL AS INT) AS Id,
CAST(NULL AS VARCHAR(256)) AS Name,
CAST(NULL AS VARCHAR(256)) AS Type,
CAST(NULL AS BIT) AS HasDefault,
CAST(NULL AS VARCHAR(MAX)) AS VALUE
WHERE 1 = 2
RETURN
END
SELECT @DeclareSQL = SUBSTRING(@SQL, 1, lst + AsFnd + 2) + '
'
FROM (SELECT PATINDEX ('%' + @WhiteSpace + @LastParam + @WhiteSpace + '%', @SQL) AS Lst) l
CROSS APPLY (SELECT SUBSTRING (@SQL, lst, LEN (@SQL)) AS SQL2) s2
CROSS APPLY (SELECT PATINDEX ('%' + @WhiteSpace + 'AS' + @WhiteSpace + '%', SQL2) AS AsFnd) af
DECLARE @ParamTable TABLE (Id INT NOT NULL,
NAME SYSNAME NULL,
TYPE VARCHAR(256) NULL,
HasDefault BIGINT NULL,
IsOutput BIT NOT NULL,
TypeName SYSNAME NOT NULL) ;
WITH pr
AS (SELECT p.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS ParameterName,
p.Parameter_id,
t.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeName,
ct.ColumnType,
MAX(Parameter_id) OVER (PARTITION BY (SELECT 0)) AS MaxParam,
p.is_output
FROM sys.parameters p (NOLOCK)
INNER JOIN sys.types t (NOLOCK) ON t.user_type_id = p.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(t.name, p.max_length, p.precision, p.scale, db.collation_name, db.collation_name) ct
WHERE OBJECT_ID = @ObjectId)
INSERT @ParamTable
(Id,
NAME,
TYPE,
HasDefault,
IsOutput,
TypeName)
SELECT Parameter_id AS Id,
ParameterName AS NAME,
ColumnType AS TYPE,
HasDefault,
is_output AS IsOutput,
TypeName
FROM pr a
CROSS APPLY (SELECT ISNULL('%' + (SELECT Util.dbo.StringConcat (ParameterName, '%') FROM pr b WHERE b.parameter_id < a.parameter_id), '') + '%'
+ ParameterName + '%=' + '%' + CASE WHEN parameter_id = MaxParam THEN @WhiteSpace + 'AS' + @WhiteSpace + '%'
ELSE (SELECT Util.dbo.StringConcat (ParameterName, '%') FROM pr b
WHERE b.parameter_id > a.parameter_id) + '%'
END AS ptt) b
CROSS APPLY (SELECT SIGN (PATINDEX (ptt, @DeclareSQL)) AS HasDefault) hd
AGAIN:
SELECT @SelValues = CASE WHEN @TableExists = 1 THEN 'INSERT #sp_ParamDefault(Id, Name, Type, HasDefault, IsOutput, Value)
' ELSE ''
END + 'SELECT * FROM (VALUES' + Util.dbo.StringConcat('(' + CAST(Id AS VARCHAR) + ', ''' + Name + ''', ''' + Type + ''', '
+ CAST(HasDefault AS VARCHAR) + ', ' + CAST(IsOutput AS VARCHAR) + ', '
+ CASE WHEN TypeName NOT LIKE '%char%' THEN 'CAST(' + name + ' AS VARCHAR(MAX))'
ELSE name
END + ')', ',
') + '
) d(Id, Name, Type, HasDefault, IsOutput, Value)',
@ExecString = 'EXEC #sp_ParamDefaultProc
' + ISNULL(Util.dbo.StringConcat(CASE WHEN HasDefault = 0 THEN Name + ' = NULL'
END, ',
'), '')
FROM @ParamTable
SET @SQL = 'CREATE PROCEDURE #sp_ParamDefaultProc
' + SUBSTRING(@DeclareSQL, CHARINDEX(@FirstParam, @DeclareSQL), LEN(@DeclareSQL)) + '
' + @SelValues
IF OBJECT_ID('TEMPDB..#sp_ParamDefaultProc') IS NOT NULL
DROP PROCEDURE #sp_ParamDefaultProc
EXEC(@SQL)
BEGIN TRY
EXEC(@ExecString)
END TRY
BEGIN CATCH
SELECT @ErrorStr = ERROR_MESSAGE(),
@ErrorId = ERROR_NUMBER()
-- there must have been a comment containing equal sign between parameters
UPDATE p
SET HasDefault = 0
FROM (SELECT PATINDEX ('%expects parameter ''@%', @ErrorStr) AS ii) i
CROSS APPLY (SELECT CHARINDEX ('''', @ErrorStr, ii + 20) AS uu) u
INNER JOIN @ParamTable p ON p.name = SUBSTRING(@ErrorStr, ii + 19, uu - ii - 19)
WHERE ii > 0
IF @@ROWCOUNT > 0
GOTO AGAIN
RAISERROR(@ErrorStr, 16, 1)
RETURN 30
END CATCH
GO
EXEC sys.sp_MS_marksystemobject
sp_ParamDefault
GO
For stored procedures, I believe you would have to write something that parses T-SQL, or use the T-SQL parser that Microsoft provides.
The parser and script generator live in two assemblies. The
Microsoft.Data.Schema.ScriptDomcontains provider agnostic classes and theMicrosoft.Data.Schema.ScriptDom.Sqlassembly contain classes for the parser and script generator that are SQL Server specific.
How to specifically use this to identify the parameters and whether they're defaulted isn't covered and would be something you'd have to work on (probably with a deal of effort) using the sample code.
加载中,请稍侯......
精彩评论