测试表是否有主键 [英] test if table has primary key

问题描述

我有以下存储过程来测试表是否有主键或没有

  ALTER  < span class =code-keyword> Procedure  [dbo]。[HasPrimaryKey] 
  @ TableName   nvarchar （ 255 ）
  as  
 选择 案例  名称<> ' ' 然后  1  其他  0   end  
  FROM  sysobjects 
  WHERE  xtype = '  PK' 
  AND  parent_obj =  OBJECT_ID （ @ TableName ）

i在Ado.net中调用此程序如下：

  public   bool  HasPrimaryKey（ string  TableName）
 {
  int  result =  0 ; 
 使用（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 ）
 返回  true ; 
  else  
 
  return  假; 
} 

我的问题是当程序返回1并选择返回值时，每件事情都可以，但是当select返回没有值时我没有将exceptionObject引用设置为对象的实例。

我该怎么办？

解决方案

只需更改存储过程以在表格不存在时返回值:)

  alter   procedure  [dbo]。[HasPrimaryKey]  @TableName   nvarchar （ 255 ）
  as  
 开始 
 如果 存在（
 选择  top   1   1  
 来自 sysobjects 
 其中 xtype = '  D' 
 和 parent_obj = object_id（ @ TableName ））
 
  return   1  
  else  
  return   0  
  end  

您可以使用函数代替存储过程。

您可以从函数返回值。

  CREATE   FUNCTION  dbo.HasPrimaryKey（ @TableName   nvarchar  （ 255 ））
  RETURNS   INT  
  AS   BEGIN  
  DECLARE   @ result   int  
  SET   @ result  =  0  
 选择  @ result  =  1  
 来自 sysobjects 
 其中​​ xtype = '  d'; 
 和 parent_obj = object_id（ @ TableName ）
  RETURN   @ result  
  END  

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

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:

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?

解决方案

Hi,

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

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

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

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

这篇关于测试表是否有主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！