SQL Clone
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35033 Visits: 16670
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1252 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