Export SQL Table Design to Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Export SQL Table Design to Excel

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2010-01-21 : 20:32:08

Hi,

I am using MSSQL 2005.

I currently have an existing database, which I wish to export to excel file.

But I am not looking to export the data. Rather, I want to export the tables/view design to excel. This is because I prefer to do my database design using excel.

Can anyone advise me how can I go about exporting SQL table design to excel?

Thanks

Hayashi

hayashiryo
Starting Member

46 Posts

Posted - 2010-01-24 : 03:30:49

upz

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 05:31:20

I just can't imagine doing the design in Excel. It will have no validation of your design, you can't describe many aspects of the design - constraints, foreign key relationships, etc. At best you can have a list of Tables and Columns (which I agree you might want to do as a rough-draft before actually creating them in MSSQL - but it is easy enough to change them using the tools in MSSQL itself)

Perhaps I haven't understood what you are trying to achieve though?

hayashiryo
Starting Member

46 Posts

Posted - 2010-01-30 : 08:31:50

Well using excel, I can track our design. Like for each aspx page, does it satisfy the necessary data required. Write notes and comments etc. Its really for recording purposes.

Kristen
Test

22859 Posts

Posted - 2010-01-30 : 08:54:16

Ah, OK. So not so much the FKey relationships and constraints.

There are tables in the INFORMATION_SCHEMA that you can query. These will include tables about the Tables and Columns, etc.

I expect you could extract the data you needed from those.

hayashiryo
Starting Member

46 Posts

Posted - 2010-01-31 : 00:50:12

quote:
Originally posted by Kristen

Ah, OK. So not so much the FKey relationships and constraints.

There are tables in the INFORMATION_SCHEMA that you can query. These will include tables about the Tables and Columns, etc.

I expect you could extract the data you needed from those.

yup not so much on the FKey. But we'll still write that down in the excel.
Thanks for the tip on the INFORMATION_SCHEMA db. But how do I use that to export to Excel? Sorry if I sound amateuris

Kristen
Test

22859 Posts

Posted - 2010-01-31 : 04:45:47

e.g.


SELECT TABLE_TYPE, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_TYPE, TABLE_NAME


SELECT TABLE_TYPE, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ... etc ...
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_TYPE, TABLE_NAME, COLUMN_NAME

and output to delimited file and open with Excel, or just Cut & Paste to Excel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-01 : 01:27:50

Change little bit point 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

hayashiryo
Starting Member

46 Posts

Posted - 2010-02-03 : 02:06:32

quote:
Originally posted by madhivanan

Change little bit point 5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Hi Madhivana,

It's very close to what I need. I will try to modify your code to achieve what I want.

But I did run into a problem. I execute the point 5 sql, but I got tihs error.

User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]

Could Not Find D:\data_file.xls
NULL

hayashiryo
Starting Member

46 Posts

Posted - 2010-02-03 : 02:09:19

I tried to run this line of code.

exec master..xp_cmdshell 'bcp " select * from (select MemberID as MemberID,Name as Name) as t" queryout "D:\test.xls" -c -T'

And i got this error

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti
ons.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
NULL

Kristen
Test

22859 Posts

Posted - 2010-02-03 : 02:38:48

Do you not need the -SServerName parameter, or is the local machine the default?

hayashiryo
Starting Member

46 Posts

Posted - 2010-02-03 : 03:06:46

I tried the following codes and got the respective errors.

exec master..xp_cmdshell 'bcp " select * from T_MEMBER" queryout "D:\test.xls" -c -T'

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti
ons.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
NULL

exec master..xp_cmdshell 'bcp " select * from T_MEMBER" queryout "D:\test.xls" -c -T -S SQLEXPRESS\MyDB'

SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti
ons.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
NULL

Kristen
Test

22859 Posts

Posted - 2010-02-03 : 03:18:49

Is "MyDB" the instance name? Looks more like the Database name, from your example?

hayashiryo
Starting Member

46 Posts

Posted - 2010-02-03 : 03:28:32

quote:
Originally posted by Kristen

Is "MyDB" the instance name? Looks more like the Database name, from your example?

pardon me...but what is instance? I thought I needed to put the server name.

Ie when i use my SQL Server Management Studio, I see I am logged onto my local server...which is GENESIS\SQLEXPRESS

Kristen
Test

22859 Posts

Posted - 2010-02-03 : 04:20:14

Yeah, server name should be enough.

But you can have multiple "Instances" on a single server (its like separate copies of SQL Server running on one machine).

So ... your server name will be "." or "localhost" or the name of the machine.

You've used "-S SQLEXPRESS\MyDB" which looks to be part instance name, part database name?

You need to provide either the SERVER name OR, if you have multiple instances on your server, you will need to provide an unambiguous Server + Instance name.

hayashiryo
Starting Member

46 Posts

Posted - 2010-02-04 : 05:20:16

Yup. I've corrected it. It should be GENESIS\SQLEXPRESS.
Where GENESIS is my local machine. And SQLEXPRESS is the instance.

I ran this code


use MyDB
exec master..xp_cmdshell 'bcp " select * from T_Member" queryout "D:\test.xls" -c -T -S GENESIS\SQLEXPRESS'

And I got this error

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'T_Member'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
NULL

It says T_Member object not found. How should I specify my DB? (when I've already wrote the command 'use MyDB')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-04 : 05:25:25

What happens when you execute this?

exec master..xp_cmdshell 'bcp " select * from myDB..T_Member" queryout "D:\test.xls" -c -T -S GENESIS\SQLEXPRESS'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-04 : 09:54:42

Yeah, as Madhi says, its likely to have to explicitly describe the Database, as well as the Table, names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources