USE master
GO
IF OBJECT_ID('sp_TableValues') IS NULL EXEC ('CREATE PROCEDURE sp_TableValues AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_TableValues
@Table SYSNAME = NULL,
@UnionSQL VARCHAR(MAX) = NULL OUTPUT,
@ValuesSQL VARCHAR(MAX) = NULL OUTPUT,
@PrintValuesSQL BIT = 1,
@PrintUnionSQL BIT = 0,
@PrintGenerateSQL BIT = 0,
@recipients NVARCHAR(1000) = NULL,
@FilePath VARCHAR(4000) = NULL /* Will write the generated sql to the path */
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @Table = Util.dbo.TrimBothEnds(@Table)
DECLARE @OBJECT_ID INT = OBJECT_ID(CASE WHEN @Table LIKE '#%' THEN 'tempdb..' + @Table
ELSE @Table
END)
IF @OBJECT_ID IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_TableValues',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
DECLARE @Nsql NVARCHAR(MAX),
@Columns VARCHAR(MAX),
@Data VARCHAR(MAX),
@UNIONALL VARCHAR(MAX)
SELECT @Nsql = 'SELECT @Data = Util.dbo.StringConcat(''('' + ' + Util.dbo.StringConcat(Cols, ' + '', '' + ') + ' + '')'', '',
'') FROM ' + @Table + '
SELECT @UNIONALL = Util.dbo.StringConcat(''SELECT '' + ' + Util.dbo.StringConcat(Cols + ' + '' as ' + ColumnName + '''', ' + '', '' + ') + ' , ''
UNION ALL
'') FROM ' + @Table,
@Columns = Util.dbo.StringConcat(ColumnName, ', '),
@Table = TableName
FROM (SELECT t.name AS TableName,
CASE WHEN ty.name LIKE '%char%'
OR ty.name = 'sysname'
THEN 'ISNULL('''''''' + REPLACE(RTRIM(CAST(' + ColumnName + ' AS VARCHAR(' + CASE WHEN c.max_length = -1 THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR)
END + '))), '''''''', '''''''''''') + '''''''', ''NULL'')'
WHEN ty.NAME IN ('bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smallint', 'smallmoney', 'tinyint', 'bit')
THEN 'ISNULL(CAST(' + ColumnName + ' AS VARCHAR), ''NULL'')'
WHEN ty.NAME = 'uniqueidentifier' THEN 'ISNULL('''''''' + CAST(' + ColumnName + ' AS VARCHAR(256)) + '''''''', ''NULL'')'
WHEN ty.NAME LIKE '%binary%' THEN 'CONVERT(VARCHAR(MAX), ' + ColumnName + ', 1)'
ELSE 'ISNULL('''''''' + CAST(' + ColumnName + ' AS VARCHAR) + '''''''', ''NULL'')'
END AS Cols,
ColumnName
FROM sys.objects t (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK) ON t.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
INNER JOIN sys.types ty (NOLOCK) ON ty.user_type_id = c.user_type_id
CROSS APPLY (SELECT '[' + c.name + + ']' AS ColumnName) cn
WHERE t.OBJECT_ID = @OBJECT_ID
AND t.type IN ('V', 'U')
AND @Table NOT LIKE '#%'
UNION ALL
SELECT t.name AS TableName,
CASE WHEN ty.name LIKE '%char%'
OR ty.name = 'sysname'
THEN 'ISNULL('''''''' + REPLACE(RTRIM(CAST(' + ColumnName + ' AS VARCHAR(' + CASE WHEN c.max_length = -1 THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR)
END + '))), '''''''', '''''''''''') + '''''''', ''NULL'')'
WHEN ty.NAME IN ('bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smallint', 'smallmoney', 'tinyint', 'bit')
THEN 'ISNULL(CAST(' + ColumnName + ' AS VARCHAR), ''NULL'')'
WHEN ty.NAME = 'uniqueidentifier' THEN 'ISNULL('''''''' + CAST(' + ColumnName + ' AS VARCHAR(256)) + '''''''', ''NULL'')'
WHEN ty.NAME LIKE '%binary%' THEN 'CONVERT(VARCHAR(MAX), ' + ColumnName + ', 1)'
ELSE 'ISNULL('''''''' + CAST(' + ColumnName + ' AS VARCHAR) + '''''''', ''NULL'')'
END AS Cols,
ColumnName
FROM tempdb.sys.tables t (NOLOCK)
INNER JOIN tempdb.sys.schemas s (NOLOCK) ON t.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN tempdb.sys.columns c (NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
INNER JOIN tempdb.sys.types ty (NOLOCK) ON ty.user_type_id = c.user_type_id
CROSS APPLY (SELECT '[' + c.name + + ']' AS ColumnName) cn
WHERE t.OBJECT_ID = OBJECT_ID('TEMPDB..' + @Table)
AND @Table LIKE '#%') kk
GROUP BY TableName
IF @PrintGenerateSQL = 1
BEGIN
PRINT '---------------- SQL TO GENERATE DATA START ----------------'
EXEC Util.dbo.PrintLargeText
@Nsql
PRINT '---------------- SQL TO GENERATE DATA END ------------------'
PRINT ''
END
EXEC sp_executesql
@Nsql,
N'@Data varchar(max) OUTPUT, @UNIONALL VARCHAR(MAX) OUTPUT',
@Data OUTPUT,
@UNIONALL OUTPUT
SELECT @ValuesSQL = 'SELECT ' + @Columns + ' FROM (VALUES
' + @Data + ')
AS ' + Util.dbo.GetTempTableName(@Table) + ' (' + @Columns + ')',
@UnionSQL = 'SELECT ' + @Columns + ' FROM
(' + @UNIONALL + ') kk'
IF @PrintUnionSQL = 1
EXEC Util.dbo.PrintLargeText
@UnionSQL
IF @PrintValuesSQL = 1
EXEC Util.dbo.PrintLargeText
@ValuesSQL
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
@FilePath AS FilePath,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @ValuesSQL, 1)
IF @recipients <> ''
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = 'sp_TableValues',
@body = @ValuesSQL
GO
EXEC sys.sp_ms_marksystemobject sp_TableValues
GO