how to export sql database table schema into msexcel file?
Hi Friends, We need to convert all table schema presented in each database to ms excel file? We are using SQL server 2014 enterprise edition in our environment. since we don't want table records only table structure to excel format. Kindly let me know, if you have any script to doing this work? Thanks stephen
What you could do is use this script to get all the table data, and copy-paste the results to an Excel SELECT o.name AS TableName , c.name AS ColumnName , t.name AS DataType , c.Max_Length , c.Precision , c.Scale FROM sys.objects AS o INNER JOIN sys.columns AS c ON o.object_id = c.object_id INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id WHERE type = 'U' ORDER BY o.name , c.name
Given you need to pivot the column names, something like this would probably work: DECLARE @Query AS NVARCHAR(MAX) DECLARE @Columns AS NVARCHAR(MAX) --Get distinct values of the PIVOT Column SELECT @Columns= ISNULL(@Columns + ',','') + QUOTENAME(column_ID) FROM (SELECT DISTINCT Column_ID FROM sys.columns) AS Cols ORDER BY Cols.column_id --Prepare the PIVOT query using the dynamic SET @Query = N'SELECT object_name(ID) as ''TableName'', ' + @Columns + ' FROM ( select [object_id] as ''ID'', name, column_ID from sys.columns where object_id > 100 ) as source PIVOT(MAX(name) FOR column_ID IN (' + @Columns + ')) AS pvt' --Execute the Dynamic Pivot Query EXEC sp_executesql @Query Note: I borrowed the basic structure from here:
http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/ This won't give you exactly what I think you need but hopefully it'll get you started. If you want each worksheet to represent each table, you'll have to figure out a way to do that dynamically, maybe through SSIS? Someone else here might have an idea.