Help: list all tables and each table, loop through each field

sthay · September 5th, 2011, 07:22 AM

Dear Experts

I am trying to write a simple C# console app using ADO.NET that able to list all tables and each table, loop through a collection of columns.

I am using SQL server 2008. I would like a solution in either C# .NET or VB.NET; but not SQL store procedure.

Your advice is highly appreciated.

Regards
sthay
PS. below is my code that does not work the way I wanted.
---

Imports System.Data.SqlClient

Module Module1

Sub Main()
Dim connetionString As String
Dim sqlCnn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sql As String

connetionString = "Data Source=localhost;Initial Catalog=database;User ID=user;Password=password"

sqlCnn = New SqlConnection(connetionString)
Try
sqlCnn.Open()
Dim schemaTable As DataTable = sqlReader.GetSchemaTable()
Dim row As DataRow
Dim column As DataColumn

For Each row In schemaTable.Rows
For Each column In schemaTable.Columns
MsgBox(String.Format("{0} = {1}", column.ColumnName, row(column)))
Next
Next
sqlReader.Close()
sqlCmd.Dispose()
sqlCnn.Close()
Catch ex As Exception
MsgBox("Can not open connection !")
End Try

End Sub

End Module

ChristianNagel · September 7th, 2011, 04:35 PM

To display all table names you just need to get the schema information on Tables for the connection, and access the column TABLE_NAME.

Code:

            string connectionString = "data source=(local);initial catalog=database;trusted_connection=true";
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            sqlConnection.Open();
            DataTable schemaTable = sqlConnection.GetSchema("Tables");
            foreach (DataRow row in schemaTable.Rows)
            {
                Console.WriteLine(row["TABLE_NAME"]);
            }
            sqlConnection.Close();

To access both table name and column name, use the Columns schema:

Code:

            string connectionString = "data source=(local);initial catalog=database;trusted_connection=true";
            SqlConnection sqlConnection = new SqlConnection(connectionString);

            sqlConnection.Open();
            DataTable schemaTable = sqlConnection.GetSchema("Columns");
            foreach (DataRow row in schemaTable.Rows)
            {
                Console.WriteLine("{0} {1}", row["TABLE_NAME"], row["COLUMN_NAME"]);
            }
            sqlConnection.Close();

You can easily find all the information that is offered from the Columns schema by writing it to XML: schemaTable.WriteXml("filename.xml")

I hope this helps.

sthay · September 8th, 2011, 08:30 AM

Dear Christian

Thanks very much for your suggestion. I have posted my question in experts-exchange.com and no one was able to give advice as close as yours-- I thought your solution would lead me to do the rest on my own; but it turns out that it is not as simple as I thought of. I need more help from you.

Let's me try to explain you a bit more about my intention. I would like to write a console app (C# & ADO.NET) that searches for keyword (string data type) for example "username" in all database tables.

Below is my attempted code that would require your further guidance, of course if you dont mind.

---
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace console
{
class Program
{
static void Main(string[] args)
{
string strSearch, strParttern, strSQL;

strSearch = "username";

if (strSearch.IndexOf("%") != -1)
{
strParttern = " like '";
}
else
{
strParttern = " = '";
}

string connectionString = "data source=(local);initial catalog=database;trusted_connection=true";
SqlConnection sqlConnection = new SqlConnection(connectionString);

sqlConnection.Open();
DataTable schemaTable = sqlConnection.GetSchema("Columns");
foreach (DataRow row in schemaTable.Rows)
{
if (row["COLUMN_NAME"] is string) // look for text field data type only; but how?
{
strSQL = "SELECT * FROM " + row["TABLE_NAME"] + " WHERE " + row["COLUMN_NAME"] + strParttern + strSearch + "';";

using (SqlCommand sqlCommand = new SqlCommand(strSQL, sqlConnection))
{
using(SqlDataReader reader = sqlCommand.ExecuteReader())
{
if (reader.RecordsAffected != 0)
{
for (int i = 0; i < reader.FieldCount - 1; i++ )
{
Console.WriteLine(reader[i]); //i would like to print a full record; but don't know how?

}
}
}

}
}

}
sqlConnection.Close();

}

}
}

---

In fact, I have a working code in VB and it uses DAO for your reference

---

this sub attempts to look for "TEST" string in all database tables
Sub Main()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim DaoDbEngine As New DAO.DBEngine()
Dim tbl As DAO.TableDef
Dim fld As DAO.Field

Dim strSQL, strPartten, strCompare As String
Dim x As Integer

Dim strSearch As String = "TEST"
strPartten = " = '" 'exact search

db = DaoDbEngine.OpenDatabase(strPathFile, False, False)

'loop through a collection of tables
For Each table In db.TableDefs

For Each field In table.Fields
If field.Type = 10 Then 'search only text field data type

strSQL = "SELECT * FROM " & table.name & " WHERE " & _
field.name & strPartten & strSearch & "';"

rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then 'if strSearch is found
Console.WriteLine("table name: " & table.name)
For x = 0 To rs.Fields.Count - 1 'displays a found record fully
Console.WriteLine(rs(x).SourceField & ", " & rs(x).Value)
Next x
Exit For
End If
rs.Close()
End If
Next
Next

End Sub

sthay · September 8th, 2011, 08:47 AM

Just have gone through your twitter and your website address. I realized how busy you are and I feel sorry to ask you such additional question.