Reading schema name, table name, and column name From SQL database and return to C # code

Question

I have a database (in SQL Server 2008 SP3) and I need all the schema names, table names and column names in the related hierarchy in C # code, I have the SQLElement class as follows:

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

And there is a list like:

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

So, how can I read Names from the database and add them to this list (SQLElementCollection)?

for example, suppose we create a table as follows:

 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 a 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 anyone have any ideas about this?

Edit:

In the next step, can we get the type of each column or the properties associated with it?

Answer 1

My suggestion is to include another DataType member in the SQLElement if you have permission to modify or create another class with the DataType property name and then inherit from SQLElement and then store the data type name in it later use and use the query below for all the 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; 

Answer 2

Connect to your database and follow these instructions:

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

Check the results, then take a look and choose what you need.

Answer 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 need to fulfill the above request and get the results in the list

Answer 4

Take a look at the Information_Schema views in your database. They have everything you need in them.