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

Question

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.

Answer 1

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

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.

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.

Answer 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.

Answer 3

You use the SQLCommand object to execute t-sql against a 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 by using the CLR built into SQL server?

Answer 4

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.

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.

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.

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.