question

cstephen avatar image
cstephen asked

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
exceltablesql server 2014export-data
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you clarify how you want this to look? Do you want the columns in the table(s) to be columns in the excel file?
0 Likes 0 ·
yes...columns in the tables to the excel file in each databases in SQL Server
0 Likes 0 ·
So each row in a worksheet will represent a database or do you want each worksheet to represent a database?
0 Likes 0 ·
Lukasz8519 avatar image
Lukasz8519 answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.