Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sql Query to display records in all tables Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 3:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:36 AM
Points: 229, Visits: 730
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)

Post #1395000
Posted Tuesday, December 11, 2012 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 5,430, Visits: 10,099
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
Post #1395003
Posted Wednesday, December 12, 2012 10:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 6:30 PM
Points: 191, Visits: 900
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
Post #1395780
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse