How can I get a list of tables in a SQL Server database along with a primary key using C # ONLY?

Question

Given a database, how can I get a list of tables along with a primary key for each table?

Thank.

Edit: this is for the code generation tool that I am creating, I need to somehow run the SQL script only with C #.

Answer 1

It does not use T-SQL on its own. It generates it on its own, but will not be as efficient as just using the exact short T-SQL that will receive the same information.

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 "vTables" collection will be a table definition in this database named vDatabaseName.

, "" . "InPrimaryKey", true.

. -, , , , , .

Answer 2

:

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

, . , , . , .

Answer 3

(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

IndexName Null.

. () ?

- ----------------

, - . :

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

( ) :

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 converting this code to C # (not my file), you can either make it a stored procedure, or just create and send a request from your code.

Answer 4

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

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

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