Read Schema Name, Table Name and Column Name From a SQL DB and return to C# Code

Question

I have a DB (in SQL Server 2008 SP3) and need all Schema names, Table names and Column Names in related hierarchy in C# Code, I have the SQLElement Class as Following:

 public class SQLElement
{
    public string SchemaName { get; set; }
    public string TableName { get; set; }
    public string ColumnName { get; set; }
}

And have a List Like:

List<SQLElement> SQLElementCollection = new List<SQLElement>();

So how can I read Names from DB and add them to this List (SQLElementCollection)?

for example assume we create a table like this:

Create Table [General].[City] (
 [Id]           BIGINT          NOT NULL    IDENTITY(1, 1),
 [Title]        NVARCHAR(30)    NOT NULL    DEFAULT (N''),
 [Province_Id]  BIGINT          NOT NULL
)

and I need the list like:

[0]={SchemaName="General", TableName="City", ColumnName="Id"} 
[1]={SchemaName="General", TableName="City", ColumnName="Title"} 
[2]={SchemaName="General", TableName="City", ColumnName="Province_Id"} 

Does any one have any idea about this?

Edit:

In next step how we can get the type of each column or related properties?

Solution 1

My suggestion is to include another member DataType in SQLElement if you have change permission or create another class with a property name DataTypeand then inherit from SQLElement and then save data type name into it for later use and use below query for all information, thanks

SELECT t.name AS TableName,
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    c.name AS ColumnName,
    tp.name as DataType
FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.types tp ON c.system_type_id =  tp.system_type_id
ORDER BY TableName;

Solution 2

This query will give you all column names and schema name

SELECT t.name AS tblName,
SCHEMA_NAME(schema_id) AS [schemaName],
c.name AS colName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where SCHEMA_NAME(schema_id) = 'dbo' // you can include this where clause  if you want to add additional filter to the result set, like query only tables that belong in particular db schema, or query only tables that starts with particular name (maybe prefix or sufix), etc.
ORDER BY tblName;

you have to execute the above query and take results in list