German Wear Discount Shop - Click Here Write for Dotnet-friends and earn for your each submission [Dot]Net-Friends
Skip Navigation Links
Home
Latest
Fast Code
Articles
Tutorials
Online Resources
Forums
Login   | Hi, Guest


Select all Table names in your Database
Written By Omer Kamal On 03/02/2008

This Fast Code is a Method which returns a DataTable filled with names of the "User created Tables" for a given Database.

Views: 1298
Rating: 5
Login to Rate
omerkamal
Tagged Under: ADO .NET, Database, SQL Server 2005

Explanation:

 

C# Code:

   protected DataTable SelectCmd()
    {
        string myConnStr = ConfigurationManager.ConnectionStrings["Personal"].ConnectionString;
        string qurey = @"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA ='dbo'";
        try
        {
            using (SqlConnection connection = new SqlConnection(myConnStr))
            {
                using (SqlCommand command = new SqlCommand(qurey, connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();

                    SqlDataReader reader = command.ExecuteReader();                  
                    DataTable myTable = new DataTable("myTable");                   
                    myTable.Load(reader);

                       return myTable;              
                }
            }
        }
        catch (Exception exp)
        {
            msg.Text = "Error Message:" + exp.Message;
            return null;
        }          
    }
                                               

VB Code:

                                                                                                  
    Protected Function SelectTableObjectsCmd() As DataTable 
    Dim myConnStr As String = ConfigurationManager.ConnectionStrings("ConectionStringNameHere").ConnectionString 
    Dim qurey As String = "SELECT [name] FROM sysobjects WHERE [type] = 'U' AND OBJECTPROPERTY(id, 'IsUserTable') = 1" 
    Try 
        Using connection As New SqlConnection(myConnStr) 
            Using command As New SqlCommand(qurey, connection) 
                command.CommandType = CommandType.Text 
                connection.Open() 
                
                Dim reader As SqlDataReader = command.ExecuteReader() 
                Dim myTable As New DataTable("myTable") 
                myTable.Load(reader) 
                
                Return myTable 
            End Using 
        End Using 
    Catch exp As Exception 
        Response.Write("Error Message:" + exp.Message) 
        Return Nothing 
    End Try 
End Function                                                     
                                                    
Delicious Digg reddit reddit Technorati
About the Author:

@@ Omer Kamal is a Software Developer at Elanize KG Germany. He MSc. Mathematics from Islamia University Bahawalpur, Pakistan and Certified Developer from National Institute of Electronics Islamabad, Pakistan. He is Founder of FriendsPoint.de and Dotnet-Friends.com. He is currently Involved with Microsoft Office SharePoint 2007, Microsoft Dynamics CRM, BI Portal Solutions (Microsoft Dynamics Customization) and Web Security Solutions.
Check Omer Kamal Profile

Related Useful Links:
Visitors/Readers Comments
(for questions please use The Forum)



Lamis Radman
Dear Auther

I want to know how could I return all user tables names and their fildes by using visual basic 6.0 please give me the answer

11/06/2008 02:49:21 UTC

adil umar
excellent :)

02/08/2008 10:00:13 UTC




Add your Comments

Name:  
Message:
Note: For faster response please use Forums >> for your questions instead of the comments area! (Admin)