get table name

Hi

I need a sql command that will give me the tables in a database.

Thanks in advance.

Cemal

Try this:

SELECT [name]FROMSYSOBJECTSWHERE xtype ='U'

|||

Or this:

SELECT TABLE_NAMEFROMINFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA='dbo'AND TABLE_TYPE='BASE TABLE'

|||

here is the error message I get when I try to run my code which you will see here.

Server Error in '/' Application.

---

The Microsoft Jet database engine cannot open the file 'INFORMATION_SCHEMA'. It is already opened exclusively by another user, or you need permission to view its data.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file 'INFORMATION_SCHEMA'. It is already opened exclusively by another user, or you need permission to view its data.

Source Error:

Line 41: ' Execute the command returning a Data Reader Line 42: Dim reader As OleDbDataReaderLine 43: reader = cmd.ExecuteReader()Line 44: ' Enuermate all the rows of data in the sheet Line 45: Dim i As Integer = 0

'------------------

' Excel File:

'

Dim sFileAsString =Me.RadioButtonList1.SelectedValue

Dim conAsNew OleDbConnection

Dim cmdAs OleDbCommand

' Create an OLEDB connection to the Excel Workbook

'I am trying both below script and get same error.

' con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFile + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

con.ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFile +";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

' Open the connection

con.Open()

' Create a Command object to get all data from the Worksheet

cmd =New OleDbCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='dbo' AND TABLE_TYPE ='BASE TABLE'", con)

' Execute the command returning a Data Reader

Dim readerAs OleDbDataReader

reader = cmd.ExecuteReader()

' Enuermate all the rows of data in the sheet

Dim iAsInteger = 0

While reader.Read()

' Display the contents of the first column

For i = 0To reader.FieldCount - 1

Response.Write(reader.GetString(i))

Next

EndWhile

' Close the reader

reader.Close()

' Close the connection

con.Close()

|||

Have you tried the solution I posted above?

|||

Patron, are you using a Microsoft Access database? If so, try:

select name from msysobjects where type = 1 and lvprop <> null;

|||

yes I tried yours, I got same problem which says table (known as the excel sheet) is not accessable becuase it is open by another user. etc.

I checked my web hosting account and made sure, I had read and write access to the file .

Can you provide anymore help on this?

Thanks in advance,.

CEMAL

|||

nanotasher:

Microsoft Access database

Ah good catch.

|||

no my codes and site is on a remote hosting company.

and I am trying to access to an excel file. Just want to read its contents with oledbreader

|||

Are you trying to read the names of which worksheets are contained within an Excel workbook? Or trying to read the contents of a single worksheet?

|||

yes,. First I want to get the worksheet name (each file will have only one worksheet) then I am gonna use it to access it's contents.

Please let me know if I am doing wrong. With this script my main goal is to read each excel file and import them into sql server table. (after check if exist any of the records)

anyway, please let me know what you think.

thanks

Cemal

|||

First, you have to have MDAC installed. I'm going to assume you have done this already.

Second, you would get at the contents by using some code like this:

using System.Data.OleDb;
using System.Data;

privatevoid Populate()
{
if (txtFile.Text.Length > 0)
{
try
{
string connectionString =@."Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + txtFile.Text +";Extended Properties= \"Excel 8.0;HDR=YES;\"";

OleDbConnection connection=newOleDbConnection(connectionString);
connection.Open();

// connection.GetSchema("Tables") will return a datatable chock full of the names of each worksheet.. Get the TABLE_NAME column
OleDbCommand command=newOleDbCommand("SELECT * FROM [" + connection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString() +]", connection);
OleDbDataAdapter oda=newOleDbDataAdapter();

oda.SelectCommand = command;
oda.Fill(ds);

connection.Close();

grdResult.DataSource = ds;
}
catch
{
Clear();
}
}
}

privatevoid Clear()
{
grdResult.DataSource =null;
}

This was a small example I wrote in a test harness application. You'll likely need to modify it to get it to work with what you're doing. The point is, you can read the results directly into a dataset pretty easily.

|||

Hi Guys,

First of all I want to say, I appriciate all the help you are trying to provide.

During all of my tries I wasn't able to make the script work for one or another reason. First of all I tried, sql commands that didn't work (neither one of them)

later on I found this getschema object of vb.net and started practicing on it. I pasted the code below.

Dim slocAsString = Request.MapPath("importfiles")

Dim strConn2AsString ="Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" + sloc +"\" +Me.RadioButtonList1.SelectedValue & _

";Extended Properties=""Excel 8.0;"""

'Response.Write(strConn2)

Dim restrictions(3)AsString

vn.ConnectionString = strConn2

vn.Open()

Dim tableAs DataTable = vn.GetSchema("Tables", restrictions)

Response.Write(table.Columns.Item(2).DefaultValue) ' THIS LINE GAVE ME NO INFORMATION AT ALL. BECUASE I DON'T KNOW HOW TO GET A HOLD OF THE OBJECT.

GridView1.DataSource = table

GridView1.DataBind()

table.Dispose()

vn.Close()

TABLE_CATALOGTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_GUIDDESCRIPTIONTABLE_PROPIDDATE_CREATEDDATE_MODIFIED 'bagscarrycasesA$'TABLE 8/9/2007 11:10:21 AM8/9/2007 11:10:21 AM

this is the result I get I after I run the script.

Now, I only need help getting the table_name value where it shows 'bagscarrycasesA$'

Thanks in advance.

|||

Never Mind guys.

I change ;

Response.Write(table.Rows(0).Item("TABLE_NAME"))

and it worked . Now I can get the excel sheet name.

thanks for all your help.

|||

Hey man thanks for that help.