GetGenericReportData.sql

CREATE OR ALTER PROCEDURE [dbo].[GetGenericReportData]
(
	@Schema VARCHAR(50)
	, @Table VARCHAR(50)	
)

AS

BEGIN

	-- Create a Numbers table. The TOP {N} value below specifies the maximum number of columns that can be rendered.
	DECLARE @nums TABLE (Num SMALLINT, ColumnID AS 'Column' + RIGHT('000' + CAST(num AS VARCHAR(10)), 3), ColumnName AS 'CAST('''' AS VARCHAR(200))')
	INSERT @nums 
	SELECT TOP 25 ROW_NUMBER() OVER (ORDER BY column_id)
	FROM sys.columns

	DECLARE @query NVARCHAR(MAX) = ''
	DECLARE @ColumnID VARCHAR(50) = '-1'
	DECLARE @ColumnName VARCHAR(50) = '-1'

	-- Use a cursor to loop through the columns for the specified table to build the SQL statement
	DECLARE db_cursor CURSOR FOR 
	
	SELECT COALESCE(q.ColumnID, n.ColumnID) [ColumnID]
		, COALESCE(q.ColumnName, n.ColumnName) [ColumnName]
	FROM @nums n
	LEFT JOIN (
		SELECT 'Column' + RIGHT('00' + CAST(ROW_NUMBER() OVER (ORDER BY c.column_id) AS VARCHAR(10)), 3) [ColumnID] 
			, c.name [ColumnName]
		FROM sys.schemas s
		INNER JOIN sys.objects o
			ON s.schema_id = o.schema_id
		INNER JOIN sys.columns c
			ON c.object_id = o.object_id
		WHERE o.type IN ('U')
			AND s.name = @Schema
			AND o.name = @Table
	) q
		ON q.ColumnID = n.ColumnID
	ORDER BY n.Num

	OPEN db_cursor  
	FETCH NEXT FROM db_cursor INTO @ColumnID, @ColumnName  

	-- Build the SELECT statement
	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		IF @query = ''
			SET @query = 'SELECT '
		ELSE
			SET @query += ', '

		SET @query += @ColumnName + ' AS [' + @ColumnID + ']'

		FETCH NEXT FROM db_cursor INTO @ColumnID, @ColumnName  
	END 

	CLOSE db_cursor  
	DEALLOCATE db_cursor

	SET @query += ' FROM [' + @Schema + '].[' + @Table + ']'

	EXEC sp_executesql @query

END
GO