Sql Query to display records in all tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Sql Query to display records in all tables

Sql Query to display records in all tables

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Tuesday, December 11, 2012 3:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:30 AM
Points: 124, Visits: 371

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

John Mitchell-245523

Posted Tuesday, December 11, 2012 3:42 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 4,434, Visits: 7,219

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

vikingDBA

Posted Wednesday, December 12, 2012 10:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:34 AM
Points: 189, Visits: 864

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 »

Permissions