test if table has primary key

Question

0.00/5 (No votes)

See more:

i have the following stored procedure which tests if table has primary key or no

SQL

ALTER Procedure [dbo].[HasPrimaryKey]
@TableName nvarchar(255)
as
select  case when  name<>'' then   1 else 0 end
FROM   sysobjects
WHERE    xtype = 'PK'
AND parent_obj = OBJECT_ID(@TableName )

i call this procedure in Ado.net as follow:

C#

public bool HasPrimaryKey(string TableName)
       {
           int result =0;
           using (SqlConnection connection = new SqlConnection(connection_string))
           {
               connection.Open();
               SqlCommand command = new SqlCommand(HasPrimaryKey, connection);
               command.CommandType = CommandType.StoredProcedure;
               command.Parameters.Add(@TableName, SqlDbType.VarChar).Value = TableName;
               result = (int)command.ExecuteScalar();
               connection.Close();

           }
           if (result == 1)
               return true;
           else

               return false;
       }

my question is when the procedure returns 1 and select returns value every thing goes ok but when select return no value i got the exceptionObject reference not set to an instance of an object.
what should i do?

Posted 29-Dec-14 20:59pm

oula alsheikh

Updated 3-Jan-15 2:28am

v5

Add a Solution

Comments

Arjsrya 30-Dec-14 4:21am

What is happening when the table has no primary key?

1.If the table has primary key it will return 1
2.If the table has no primary key then it will return 0

If you don't pass the correct table name to the store procedure then obviously it will throw exception or return null so how are you handling this problem in your code?

oula alsheikh 30-Dec-14 5:51am

iam sure that the table name is correct its not my problem
i got the exception maybe when there is no select
available am i right?

[no name] 30-Dec-14 6:53am

If your table is not having any value then also you can return some customized value like 0. In this way your issue will be resolved.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BechirBenltaief · Answer 1 · 2014-12-29T23:44:00

Hi,

just change your stored procedure to return value when the table doesn't existe :)

SQL

alter procedure [dbo].[HasPrimaryKey] @TableName nvarchar(255)
as
begin
    if exists(
        select  top 1 1
        from   sysobjects
        where    xtype = 'D'
        and parent_obj = object_id(@TableName))

        return 1
    else
        return 0
end

Sumit Jawale · Answer 2 · 2014-12-30T00:15:00

Solution 2

Instead of stored procedure you can use function.
you can return value from function.

SQL

CREATE FUNCTION dbo.HasPrimaryKey (@TableName nvarchar(255))
RETURNS INT
AS BEGIN
    DECLARE @result int
    SET  @result = 0 
    select @result = 1 
        from   sysobjects
        where    xtype = 'D';
        and parent_obj = object_id(@TableName)
    RETURN @result
END

Posted 30-Dec-14 0:15am

Sumit Jawale

Updated 30-Dec-14 0:16am

v2

Comments

oula alsheikh 3-Jan-15 8:35am

i tried both solutions and the problem still appears

Sumit Jawale 4-Jan-15 23:44pm

You can try one more thing, instead of taking int variable for result try it with string.

public bool HasPrimaryKey(string TableName)
{
string result = "";
using (SqlConnection connection = new SqlConnection(connection_string))
{
connection.Open();
SqlCommand command = new SqlCommand(HasPrimaryKey, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(@TableName, SqlDbType.VarChar).Value = TableName;
result = convert.ToString(command.ExecuteScalar());
connection.Close();

}
if (result == "1")
return true;
else
return false;
}

oula alsheikh 5-Jan-15 7:30am

Thanks very much Sumit i think it is going to be unsolved
what confused me that i have another function that is similar to that function and it works in normal way but this didn't what is the reason ?

Sumit Jawale 5-Jan-15 7:39am

In your other Function, are you calling same procedure "HasPrimaryKey" ??

oula alsheikh 6-Jan-15 7:09am

No iam using another procedure but it is written in same way and it works fine

Sumit Jawale 6-Jan-15 7:21am

ok.. then it is difficult to identify exact cause. :(
Try to find out exact cause by yourself.and please share if you find it. :)