advanced code snippet search

Posted By

niaher on 09/23/10


Tagged

sql MS


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


Print all row values from any table using SQL

 / Published in: SQL
 

URL: http://www.lessonist.com/lessons/id/594/print-all-row-values-from-any-table-using-sql

Here is the SQL code to print a row of from any table. All you have to do is modify the @tableName and @whereClause variables. Works only in MS SQL 2005+

Expand | Embed | Plain Text 
  1. DECLARE @tableName varchar(100)
  2. DECLARE @whereClause varchar(100)
  3.  
  4. SET @tableName = 'Customers'
  5. SET @whereClause = 'WHERE id = 999'
  6.  
  7. DECLARE @TABLE TABLE(id int identity(1, 1), name varchar(100))
  8. INSERT INTO @TABLE (name)
  9. SELECT name FROM sys.COLUMNS WHERE object_id = OBJECT_ID(@tableName)
  10.  
  11. DECLARE @i int
  12. DECLARE @rowCount int
  13. SELECT @i = MIN(id), @rowCount = MAX(id) FROM @TABLE
  14.  
  15. DECLARE @rowString varchar(MAX)
  16. SET @rowString = ''
  17.  
  18. WHILE (@i <= @rowCount)
  19. BEGIN
  20. DECLARE @columnName varchar(100)
  21. SELECT @columnName = name FROM @TABLE WHERE id = @i
  22.  
  23. DECLARE @columnValueTable TABLE(name varchar(100))
  24. INSERT INTO @columnValueTable
  25. EXEC ('SELECT ' + @columnName + ' FROM ' + @tableName + ' ' + @whereClause)
  26.  
  27. DECLARE @columnValue varchar(100)	
  28. SELECT @columnValue = name FROM @columnValueTable
  29.  
  30. IF (@columnValue IS NULL) SET @columnValue = 'NULL'
  31.  
  32. SET @rowString = @rowString + @columnName + ': ' + @columnValue + CHAR(10)
  33.  
  34. SET @i = @i + 1
  35. END
  36.  
  37. -- You can also use SELECT instead of PRINT.
  38. PRINT @rowString

Report this snippet  

You need to login to post a comment.