Read Schema Name, Table Name and Column Name From

问题:

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?

回答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;

回答2:

Connect to your database and execute the following statement:

select * 
from INFORMATION_SCHEMA.COLUMNS 
order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

Check the results and then peek and choose what you need.

回答3:

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

回答4:

Have a look at the Information_Schema Views in your DB. These have all the stuff you want already in them.