InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 22:35:00 GMT20http://www.sqlservercentral.com/Forums/Topic1395000-391-1.aspxTry 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 OFFWed, 12 Dec 2012 10:04:50 GMTvikingDBAhttp://www.sqlservercentral.com/Forums/Topic1395000-391-1.aspxNo, 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.JohnTue, 11 Dec 2012 03:42:53 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1395000-391-1.aspxHi Team,I need a query to display all the records from all tables in a databaseor export records tables wise to excelam 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.tablesEXEC(@sqlText)Tue, 11 Dec 2012 03:34:57 GMTMinnu