Advanced Search

Print all row values from any table using SQL

/ Published in: SQL
Save to your folder(s)
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

Copy this code and paste it in your HTML 
  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

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.