sp_ViewData
/*
select * from information_schema.tables
select * from information_schema.columns
*/
ALTER PROC Sp_viewdata (@schema NVARCHAR(100) =‘dbo’,
@table_name NVARCHAR(100),
@where NVARCHAR(MAX))
AS
/*
Test:
sp_ViewData @table_name =’CostDetail’,@where = ’CostDetailsDVEID = 665564′
*/
DECLARE @sql NVARCHAR(MAX),
@column_name NVARCHAR(MAX)
SELECT table_schema + ‘.’ + table_name AS table_name,
column_name,
data_type,
character_maximum_length size,
VALUE = CAST(NULL AS VARCHAR(MAX))
INTO #temp
FROM information_schema.columns
WHERE table_name = @table_name
AND table_schema = @schema
AND Charindex(‘_RAW’, column_name) = 0
ORDER BY column_name
DECLARE t_cursor CURSOR FOR
SELECT table_name,
column_name
FROM #temp
OPEN t_cursor
-- Perform the first fetch.
FETCH NEXT FROM t_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM t_cursor INTO @table_name, @column_name
SET @sql = ‘UPDATE #temp SET value = (SELECT CONVERT(VARCHAR(max),’ +
@column_name +
‘) FROM ’ + @table_name + ‘ WHERE ’ + @where + ‘)’
+
‘ WHERE column_name = ”’ + @column_name + ””
PRINT @sql
EXEC Sp_executesql @stmt = @sql
END
CLOSE t_cursor
DEALLOCATE t_cursor
SELECT *
FROM #temp
GO
