SQL Server: How to iterate over function arguments

Question

I'm trying to learn SQL and for demonstration purposes I would like to create a loop which iterates over function arguments. E.g. I would like to iterate over SERVERPROPERTY function arguments (propertynames). I can do single select like this:

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO

but how to iterate over ALL propertynames of this built-in-funciton? Thanks in advance.

Answer 1

As far as I know I don't think you can do it. It is a system function with set PropertyNames to get specific details.

HTH

Answer 2

I dont think theres a built in way of doing it but have a look here to see if this does what you need: http://www.sqlservercentral.com/scripts/Replication/31229/

Answer 3

you can use INFORMATION_SCHEMA.PARAMETERS to see the parameters of user created objects.

However SERVERPROPERTY isn't going to show up in that. However, what you are asking for, is not to iterate over the parameters, but over the actual values of the propertyname parameter of SERVERPROPERTY.

SERVERPROPERTY ( propertyname )

The values are documented and should not change until you have another version of SQL Server installed, so hard code and create your cursor on this:

SELECT SERVERPROPERTY('ProductVersion') AS Value , 'ProductVersion' as TypeOf
UNION ALL SELECT SERVERPROPERTY('ProductLevel')  , 'ProductLevel'
UNION ALL SELECT SERVERPROPERTY('Edition')       , 'Edition'
UNION ALL SELECT SERVERPROPERTY('EngineEdition') , 'EngineEdition'

OUTPUT:

Value                      TypeOf            
-------------------------- ------------------
9.00.3042.00               ProductVersion    
SP2                        ProductLevel      
Standard Edition           Edition           
2                          EngineEdition     

(4 row(s) affected)

Answer 4

I would think there is a table of valid SERVERPROPERTY properties somewhere in SQL Server, but you can simply add one to your application. This routine makes a table variable, populates it and generates dynamic SQL which produces a row with columns as you wish and also converts things to their underlying base type.

SET NOCOUNT ON

DECLARE @props AS TABLE
    (
     propname VARCHAR(255)
    ,datatype VARCHAR(50)
    ) ;

INSERT  INTO @props
VALUES  (
         'ProductVersion'
        ,'nvarchar(128)' 
        ) ;
INSERT  INTO @props
VALUES  (
         'ProductLevel'
        ,'nvarchar(128)' 
        ) ;
INSERT  INTO @props
VALUES  (
         'Edition'
        ,'nvarchar(128)' 
        ) ;
INSERT  INTO @props
VALUES  (
         'EngineEdition'
        ,'int' 
        ) ;

DECLARE @sql AS VARCHAR(MAX) ;

SELECT  @sql = ( SELECT ', CAST(SERVERPROPERTY(''' + propname + ''') AS '
                        + datatype + ') AS ' + propname
                 FROM   @props
               FOR
                 XML PATH('')
               ) ;

SET @sql = 'SELECT' + STUFF(@sql, 1, 1, '') + ';' ;

PRINT @sql ; 
EXEC ( @sql
    ) ;

SELECT  @sql = ( SELECT ' UNION ALL SELECT ''' + propname
                        + ''' AS PropertyName, SERVERPROPERTY(''' + propname
                        + ''') AS PropertyValue'
                 FROM   @props
               FOR
                 XML PATH('')
               ) ;

SET @sql = STUFF(@sql, 1, LEN(' UNION ALL '), '') + ';' ;

PRINT @sql ;
EXEC ( @sql
    ) ;

I've generated it in columns and rows in this example.

Answer 5

You can do it (as you tagged) with a cursor:

/* put props in a table */
DECLARE @T TABLE (PROP VARCHAR(128))
INSERT @T
    SELECT 'ProductVersion' 
    union select 'ProductLevel'
    union select 'Edition'
    union select 'EngineEdition'

/* iterate */
DECLARE Cur CURSOR FAST_FORWARD FOR SELECT PROP FROM @T
DECLARE @PROP VARCHAR(128) 
OPEN Cur

FETCH NEXT FROM Cur INTO @PROP

WHILE @@FETCH_STATUS = 0 BEGIN
  SELECT @PROP + ' is ' + CAST(SERVERPROPERTY(@PROP) AS VARCHAR(128))
  FETCH NEXT FROM Cur INTO @PROP
END

CLOSE Cur
DEALLOCATE Cur 

Or once you have @T

SELECT PROP + ' is ' + CAST(SERVERPROPERTY(PROP) AS VARCHAR(128)) FROM @T