如何只使用C＃获取SQL Server数据库中的表列表以及主键？

Given a database, how can I retrieve a list of Tables along with the primary key of each table?

给定一个数据库,如何检索表列表以及每个表的主键?

Thanks.

Edit: This is for a code generation tool I'm making, I need to somehow run the SQL script using C# only.

编辑:这是我正在制作的代码生成工具,我需要以某种方式使用C#运行SQL脚本。

4 个解决方案

#1

This uses no T-SQL by itself. It generates it on its own but will not be as efficient as just using the exact, short T-SQL that will get the same information.

它本身不使用T-SQL。它自己生成它,但不会像使用将获得相同信息的确切的短T-SQL那样高效。

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

public class LoadStuff
{
    string mDatabaseConnectionString = "Something";
    ...
    public void LoadDatabase(string tDatabaseName)
    {
        using (var vSqlConnection = new SqlConnection(mDatabaseConnectionString))
        {
            var vConnection = new ServerConnection(vSqlConnection);
            var vServer = new Server(vConnection);
            var vDatabase = vServer.Databases[tDatabaseName];
            var vTables = vDatabase.Tables;
        }
    }
}

Each of the Objects in the collection "vTables" will be a definition for a Table in that database called vDatabaseName.

集合“vTables”中的每个对象都将是该数据库中名为vDatabaseName的表的定义。

That table will have a collection of columns, and the primary keys can be found be looping through the "Columns" property of each table. Any columns in the primary key will have its property "InPrimaryKey" marked true.

该表将包含一组列,并且可以找到主键循环遍历每个表的“列”属性。主键中的任何列都将其属性“InPrimaryKey”标记为true。

Also you must get all information out of the various objects BEFORE the end of the using block. I went about making mini classes that had just the information I needed once the whole process was done, but you can probably just spit out the code from the code generation instead.

此外,您必须在使用块结束之前从各种对象中获取所有信息。在完成整个过程之后,我开始制作只有我需要的信息的迷你类,但你可能只是从代码生成中吐出代码。

#2

Here's one answer:

这是一个答案:

select 
    t.Table_Name,
    tc.Constraint_Name,
    ccu.Column_Name
from
    information_schema.tables t
    left join information_schema.table_constraints tc
        on tc.Table_Catalog = t.Table_Catalog
        and tc.Table_Name = t.Table_Name
        and tc.Constraint_Type = 'PRIMARY KEY'
    left join information_schema.constraint_column_usage ccu
        on ccu.Table_Catalog = tc.Table_Catalog
        and ccu.Table_Name = tc.Table_Name
        and ccu.Constraint_Schema = tc.Constraint_Schema
        and ccu.Constraint_Name = tc.Constraint_Name
order by
    t.Table_Name,
    tc.Constraint_Name,
    ccu.Column_Name

This will list tables, their primary key constraints, and the columns in those constraints. Note that if a primary key has multiple columns, there will be multiple entries for that column. Also note that this query returns views, too.

这将列出表,它们的主键约束以及这些约束中的列。请注意,如果主键具有多个列,则该列将有多个条目。另请注意,此查询也会返回视图。

#3

Here's a start (SQL 2005 and up):

这是一个开始(SQL 2005及以上版本):

SELECT ta.name TableName, ind.name IndexName
 from sys.tables ta
  left outer join sys.indexes ind
   on ind.object_id = ta.object_id
    and ind.is_primary_key = 1

Tables without primary keys have IndexName set to Null.

没有主键的表将IndexName设置为Null。

But this merely lists the name of the primary key. Do you need a list of the column(s) in the key?

但这只列出了主键的名称。您是否需要密钥中的列列表?

-- ADDED ----------------

- 添加 - - - - - - - -

Here's a part of a utility I once wrote. This will list all tables, and columns in order of all existing primary keys:

这是我曾经写过的一个实用程序的一部分。这将按所有现有主键的顺序列出所有表和列:

SELECT ta.name TableName, ind.name IndexName, indcol.key_ordinal IndexColumnOrder, col.name ColumnName
 from sys.tables ta
  left outer join sys.indexes ind
   on ind.object_id = ta.object_id
    and ind.is_primary_key = 1
  left outer join sys.index_columns indcol
   on indcol.object_id = ta.object_id
    and indcol.index_id = ind.index_id
  left outer join sys.columns col
   on col.object_id = ta.object_id
    and col.column_id = indcol.column_id
 order by
   ta.Name
  ,indcol.key_ordinal

Hacking that, the following will list all (and only) tables with primary keys of only one column:

黑客攻击,下面将列出主键只有一列的所有(且唯一)表:

SELECT ta.name TableName, max(col.name) ColumnName
 from sys.tables ta
  inner join sys.indexes ind
   on ind.object_id = ta.object_id
    and ind.is_primary_key = 1
  inner join sys.index_columns indcol
   on indcol.object_id = ta.object_id
    and indcol.index_id = ind.index_id
  inner join sys.columns col
   on col.object_id = ta.object_id
    and col.column_id = indcol.column_id
 group by ta.name
 having count(*) = 1
 order by ta.Name

As for transforming this to C# (not my forte), you should be able to either make this a stored procedure, or just build and submit the query from within your code.

至于将其转换为C#(不是我的强项),您应该能够将其作为存储过程,或者只是在代码中构建和提交查询。

#4

You use the SQLCommand object to execute t-sql against a database..

您使用SQLCommand对象对数据库执行t-sql ..

Documented here with many examples in c# and other languages: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx

这里记录了c#和其他语言中的许多示例:http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx

Or do you mean directly by using the CLR built into SQL server?

或者你的意思是直接使用内置于SQL服务器的CLR?

#1