How-to create custom formatting for each result set returned by a stored procedure in the whole sql server 2005 database


-- So the idea is that you refer to each column and than parse the formatting elements
-- you reffer to each column by Col + ColNumber + TypeOfFormatting notation
-- to the the data you would have the query the extended properties for that stored procedure




DECLARE @procName varchar(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
select s.name from sysobjects s where type = 'P' OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
print
'

EXEC sys.sp_addextendedproperty @name=N''Col1NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col2NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col3NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col4NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col5NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col6NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col7NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col8NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col9NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col10NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col11NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col12NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col13NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col14NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col15NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col16NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col17NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col18NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col19NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col20NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''

'


FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName