CODEWITHSUNDEEP
sql-serverdatabase-designado.netuser-defined-types
Transformer
Transformer

Reputation: 7439

How to get user defined tables

I am trying to narrow down which tables I get back from querying the schema. While I can get back all tables, I saw other questions here, but they dont have info on getting user back User Defined tables, I would like to query and

  1. get back, user defined tables

  2. get back tables based on some criteria, for e.g. table with tenantId col

    public static List<string> GetUserCreatedTables(string connectionString = null)
{
    if (string.IsNullOrEmpty(connectionString))
        connectionString = Settings.Default.ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        DataTable schema = connection.GetSchema("Tables");
        List<string> TableNames = new List<string>();
        foreach (DataRow row in schema.Rows)
        {
            TableNames.Add(row[2].ToString().Trim());
        }
        return TableNames;
    }
}

I am familiar with this method, where I can query directly. SELECT Name from Sysobjects where xtype = 'u' , but I would like to adopt the previous method so I can reuse it to query others.

Upvotes: 1

Views: 865

Answers (2)

userfl89
userfl89

Reputation: 4810

You can use SMO objects to verify if the table is a user-created table and column names as well. As noted below the IsSystemObject property returns true for system objects. The Contains method of Columns can be used to check the column name. References to Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.SMO will needed to be added to the project with the Microsoft.SqlServer.Management.Smo namespace referenced. To look at views change the Table object to a View one in the first foreach loop. 

using Microsoft.SqlServer.Management.Smo;

            Server serv = new Server(@"YourServer");

            //for Windows authentication
            serv.ConnectionContext.LoginSecure = true;
            serv.ConnectionContext.StatementTimeout = 600;
            Database db = serv.Databases["YourDatabase"];

            List<string> TableNames = new List<string>();

            foreach (Table t in db.Tables)
            {
                //check for system objects
                if (!t.IsSystemObject && t.Columns.Contains("tenantId"))
                {
                    //use the Name property to return the table name
                    TableNames.Add(t.Name);
                }
            }

Upvotes: 1

Philip Cullen
Philip Cullen

Reputation: 21

If you pass StructuredTypeMembers rather than Tables to GetSchema() it will return the user defined table types and their columns.

To obtain a filtered list, you can pass in a restriction to GetSchema(). The restrictions for StructuredTypeMembers are Catalog, Owner, Table and Column. These are passed in an array, in that order, to the second parameter of GetSchema().

To get the user defined tables with a column of tenantId, you can call GetSchema() as follows:

connection.GetSchema("StructuredTypeMembers", (new[] { null, null, null, "tenantId"}));

Upvotes: 1

Related Questions