Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql Query to display records in all tables


Sql Query to display records in all tables

Author
Message
Minnu
Minnu
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 950
Hi Team,

I need a query to display all the records from all tables in a database

or

export records tables wise to excel

am using below query, but table names are not displaying.

please help.


DECLARE @sqlText VARCHAR(MAX)
SET @sqlText = ''

SELECT @sqlText = @sqlText + ' SELECT * FROM ' + QUOTENAME(name) + CHAR(13) FROM sys.tables

EXEC(@sqlText)
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7353 Visits: 15067
No, the table names won't display. If you do SELECT * FROM MyTable, that shows you everything in the table, but it doesn't return the name of the table. You'll need to build your queries in a more sophisticated way if you want to do that.

John
vikingDBA
vikingDBA
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 929
Try this script. Remember to set the context to the database you are interested in before running the script.





/* ================================================================================================================================ */
/* = Generate SELECT statements for each table (on Messages tab) = */
/* ================================================================================================================================ */
/*   Created Date: 12/12/2012
   By: VikingDBA
   Modifications:

   Dependencies:
      This script depends on the following to exist:
         none
         
   Summary:
   This script creates the SELECT statements for each table. These
   will appear on the Messages tab when it is finished running. A line
   also appears below the SELECT statement giving the data type of each
   element, in the same order as they appear in the SELECT statement.
   
*/

/*
NOTE: Remember to set the database context before running this script.
*/


   DECLARE @tablename varchar(100)
   DECLARE @schemaname varchar(100)
   DECLARE @op int
   DECLARE @wow varchar(8000)
   DECLARE @dtypelist varchar(8000)
   
   SET NOCOUNT ON
   
   
   SELECT @@SERVERNAME as 'ServerName', TABLE_CATALOG As 'DatabaseName',(select top 1 d.name from sys.data_spaces d where d.data_space_id =(select top 1 i.data_space_id from sys.indexes i where i.object_id = (SELECT top 1 t.object_id FROM sys.tables t WHERE gg.TABLE_NAME= t.name) and i.index_id < 2)) AS 'TableOnFileGroup',
   TABLE_SCHEMA AS 'SchemaName',
   TABLE_NAME AS 'TableName',
   COLUMN_NAME AS 'DataElement',
   ORDINAL_POSITION AS 'OrdinalPosition',
   DATA_TYPE AS 'DataType',
   ISNULL(CONVERT(char(10),CHARACTER_MAXIMUM_LENGTH),'') AS 'MaxLen', ISNULL(CONVERT(char(10),NUMERIC_PRECISION),'') AS 'Prec', ISNULL(CONVERT(char(10),NUMERIC_SCALE),'') AS 'Scale',
   CASE IS_NULLABLE WHEN 'YES' THEN '' ELSE 'NOT NULL' END AS 'NOTNULL',
   CASE WHEN COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 then 'YES' ELSE '' END AS 'Identity',
   ISNULL((SELECT top 1 object_name(k.referenced_object_id) + '(' + col_name(k.referenced_object_id,k.referenced_column_id) + ')' FROM sys.foreign_key_columns k WHERE object_name(k.parent_object_id) = gg.TABLE_NAME AND col_name(k.parent_object_id,k.parent_column_id) = gg.COLUMN_NAME),'') AS 'References',
   ISNULL(COLUMN_DEFAULT,'') AS 'DefaultValue'
   INTO #clmnInfoTable
   FROM INFORMATION_SCHEMA.COLUMNS gg
   ORDER BY SchemaName, TableName, OrdinalPosition
   --note that TABLE_CATALOG is the database name
   
   -- can uncomment following line if you want to see the table that was created above
   --SELECT * FROM #clmnInfoTable
   
   SELECT TOP 1 @wow = DatabaseName FROM #clmnInfoTable
   PRINT 'USE [' + @wow + ']'
   PRINT 'GO'
   PRINT ' '


   DECLARE GetTables CURSOR FOR
      SELECT    DISTINCT SchemaName , TableName
      FROM #clmnInfoTable
      ORDER BY [SchemaName], [TableName]

   OPEN GetTables
   SET NOCOUNT ON

   -- Loop through all the schemas and tablenames
   FETCH NEXT FROM GetTables INTO @schemaname, @tablename

   WHILE @@FETCH_STATUS = 0
   BEGIN

      PRINT '-- For table ' + @schemaname + '.' + @tablename
      PRINT '-- CREATE VIEW ' + @schemaname + '.' + 'vw' + @tablename + ' AS '
      SET @wow = 'SELECT '
      SET @dtypelist = ''
      SELECT @wow = @wow + '[' + DataElement + '],', @dtypelist = @dtypelist + [DataType] + ','
      FROM #clmnInfoTable
      WHERE SchemaName = @schemaname AND TableName = @tablename
      ORDER BY OrdinalPosition
      PRINT SUBSTRING(@wow,1,LEN(@wow)-1) + ' FROM ' + @schemaname + '.' + @tablename
      PRINT '-- ' + SUBSTRING(@dtypelist,1,LEN(@dtypelist)-1)
      PRINT ' '



      FETCH NEXT FROM GetTables INTO @schemaname, @tablename
   END

   CLOSE GetTables
   DEALLOCATE GetTables
   
   
   DROP TABLE #clmnInfoTable

   SET NOCOUNT OFF
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search