How to retrieve user table name in sql using vb 2010

Question

0.00/5 (No votes)

See more:

I have connection string like this

VB.NET

Dim sqlConn As New SqlClient.SqlConnection("Data Source=C8067;Initial Catalog=msdb;Integrated Security=True")

This is a local database where i did not specify any schema while creating it.

now i want to retrieve only table names which I created.

I tried this but it retrieves all tables which i do not require like this

VB.NET

Dim cmd As New SqlClient.SqlCommand("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'", sqlConn)

Result

syspolicy_policies_internal
sysproxies
sysdbmaintplan_history
backupmediaset
sysdownloadlist
sysjobhistory
backupset
regions

Please help me filter. I want to only tables I created

Posted 10-Jan-16 22:32pm

vivianpinto

Add a Solution

1 solution

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)

DamithSL · Answer 1 · 2016-01-10T22:43:00

Solution 1

SQL

SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'

Posted 10-Jan-16 22:43pm

DamithSL

Comments

vivianpinto 11-Jan-16 20:50pm

Thank you Damit

It gives me the same list even if i comment the sql command querry. this is strange.
Am I wrong in my code at looping.? why it gives me result even if comment the sql querry?

<pre lang="C#">Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConn As New SqlClient.SqlConnection("Data Source=C8067;Initial Catalog=msdb;Integrated Security=True")
sqlConn.Open()

'Dim cmd As New SqlClient.SqlCommand("SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = 'U'", sqlConn)
Dim dsColumns As New DataSet
Dim daAdapter As New SqlClient.SqlDataAdapter(cmd)

daAdapter.Fill(dsColumns)
If dsColumns.Tables(0).Rows.Count > 0 Then
ListBox1.Items.Clear()
For i As Integer = 0 To dsColumns.Tables(0).Rows.Count - 1
ListBox1.Items.Add(dsColumns.Tables(0).Rows(i)(0).ToString())

Next
Else
MessageBox.Show("Tables not found.")
End If
sqlConn.Close()

DamithSL 12-Jan-16 0:30am

you should get compile error for above code