December 11, 2012 at 3:34 am
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)
December 11, 2012 at 3:42 am
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
December 12, 2012 at 10:04 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply