How to Read all the tables in the database and display fields in Excel ?

Question

0.00/5 (No votes)

See more:

, +

Hi Friends,
Can Anyone help me with Idea ? I am having an Default.aspx Page ( UI Page ) with four textbox and a button(Show in Excel) ? The Textbox are 1. ServerNameTextbox 2. DatabaseNameTextBox 3.UserNametextbox 4.PasswordTextbox . And My Need is If I give the servername,databasename,username,password values in the textbox and click the Button then I need to get all details about the database from sql server with table name, column name and column datatypes and bind this information in Excel ??

Posted 6-Feb-14 21:24pm

MurugappanCTS

Add a Solution

2 solutions

Solution 1

The problem is that you can't "show in excel" on the client directly: you can send the client an Excel file, but it's up to him if he wants to open it, save it or if he even has Excel installed.

You can use Excel in your code, but it will run on the Server, not the Client, so it production it won't be available for the user to view.

Posted 6-Feb-14 21:38pm

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

♥…ЯҠ…♥ · Accepted Answer · 2014-02-06T21:45:00

Hi Murugappan,

Use DatabaseName,Username and password to check the connection by passing it with connection string,
If connection is successful then use the below query to fetch the details about database

SQL

USE DatabaseName;
SELECT * FROM sys.objects WHERE type_desc LIKE '%FUNCTION%' OR type_desc IN ('USER_TABLE','SQL_STORED_PROCEDURE','VIEW') order by type_desc asc;

the above query will fetch all details that belongs to the Database given

If you want to fetch on category based then you can make use of the below queries

SQL

USE DatabaseName;
SELECT * FROM sys.tables
SELECT * FROM sys.procedures
SELECT * FROM sys.views
SELECT * FROM sys.objects WHERE type_desc LIKE '%FUNCTION%';

Once fetched you can export it into Excel using server side code.You can refer here[^] for this.
Hope this helps you a bit.

Regards,
RK