recognize stored procedure and table in sql server

Question

I get a SQL Data Base Connection String from user then I want to check for a particular Table or special stored procedure in the database?

How can I do this in C#?

Edited the grammar. Are you in other words, trying to query the schema(tables etc) of your database? — gideon, Jan 05 '12 at 07:24
This question has two parts, both dupes: http://stackoverflow.com/questions/1266960/sql-query-to-search-schema-of-all-tables http://stackoverflow.com/questions/291574/query-to-list-sql-server-stored-procedures-along-with-lines-of-code-for-each-pro — gideon, Jan 05 '12 at 07:26
Also see simple db schema query example here : http://blog.sqlauthority.com/2007/06/26/sql-server-2005-list-all-tables-of-database/ — gideon, Jan 05 '12 at 07:27

competent_tech · Accepted Answer · 2012-01-05T16:29:50.333

The shortcut for this is (in SQL):

SELECT OBJECT_ID('tableName')

or

SELECT OBJECT_ID('storedprocedurename')

If these return null (DBNull.Value), then the item doesn't exist. Otherwise, it does.

So, in C#, that would be something like:

        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            var cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"SELECT OBJECT_ID('" + MyObjectName + @"')";
            if (cmd.ExecuteScalar() == DBNull.Value)
            {
                Console.WriteLine("Does not exist");
            }
            else 
            {
                Console.WriteLine("Does exist");
            }
        }

recognize stored procedure and table in sql server

1 Answers1