Retrieving SQL Server database schema and objects and reflect in a .Net application

Question

What is the simplest way to reflect the SQL Server database schema with all its objects in a .Net application?

Basically I would like my application to scan the database schema and present me all tables and other objects. Further I should be able to explore all table columns and their properties (type, constraints, etc.) as well.

I am planning to use Data Access Application Block for my data access needs. Would it also meet the above stated requirement?

score 2 · Answer 1 · answered Apr 01 '11 at 06:58

2

You can use any data access method you want - you just need to run the right queries on the database. This article seems to have a good example of what you want.

answered Apr 01 '11 at 06:58

cbp

25,252
29
125
205

score 2 · Answer 2 · answered Apr 01 '11 at 06:58

2

SQL Server provides some system views that can be used, e.g. sys.tables lists all tables, sys.columns all columns etc.

MSDN has a FAQ.

answered Apr 01 '11 at 06:58

Anders Abel

67,989
17
150
217

score 1 · Answer 3 · answered Apr 01 '11 at 07:54

Although you can use queries on the metadata tables of SQL Server, the cleanest way to access this information is by using Sql Server Management Objects (SMO).

To use this example, reference the Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc and Microsoft.SqlServer.Smo.

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

   var sqlConnection = new SqlConnection(@"Data Source=(local);Integrated Security=SSPI");
    var server = new Server(new ServerConnection(sqlConnection));

    foreach (Database database in server.Databases)
    {
        foreach (Table table in database.Tables)
        {
            Console.WriteLine("{0}: {1}", database.Name, table.Name);       
        }
    }

Retrieving SQL Server database schema and objects and reflect in a .Net application

3 Answers3