Technical: Microsoft – SQL Server – Get Primary Key Columns

Technical: Microsoft – SQL Server – Get Primary Key Columns

Using sp_help



Syntax:

   exec sp_help '<object-name>'

Sample:

   exec sp_help 'dbo.Customer'

Table -- sp_help (v2)

Explanation:

  • Check the RowGuidColumn section
  • Check the Index section and look for any entries bearing “Primary Key”
  • Check the Constrain section and look for any entries bearing “PRIMARY KEY”

Using sp_helpindex



Syntax:

   exec sp_helpindex '<schema-name>.<object-name>'

   exec sp_helpindex 'dbo.customer' 

 

Output:

Table -- sp_helpindex

Using sp_pkeys



Syntax:

   exec sp_pkeys '<object-name>', '<schema-name>'

Sample:

   exec sp_pkeys 'Customer', 'dbo'

Output:

Table -- sp_pkeys

 

 

sp_primary_keys

Not aware of this SP until reading SQL Server: Get table primary key using sql query [duplicate] ( http://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query ).


Syntax:



     declare @serverName sysname
     declare @databaseName sysname
     declare @is_data_access_enabled bit

     set @serverName = CAST(serverproperty('servername') as sysname)
     set @databaseName = db_name()  

     select @is_data_access_enabled = [is_data_access_enabled] 
     from   sys.servers
     where  name = @serverName
     and    is_data_access_enabled = 0

     if  (
             (@is_data_access_enabled is null) 
          or (@is_data_access_enabled = 0) 
         )    
     begin

        print 'Setting ServerOption:'
        print '
            Msg 7411, Level 16, State 1, Procedure sp_primarykeys, Line 10
            Server  is not configured for DATA ACCESS.
             '

       exec sp_serveroption @serverName 
                            , 'data access'
                            , 'true' --execute once  

    end

   exec sp_primarykeys
          @table_server = @serverName
        , @table_catalog = @databaseName 
        , @table_schema = 'dbo'
        , @table_name = 'customer' 


Sample:



Sample:

     declare @serverName sysname
     declare @databaseName sysname
     declare @is_data_access_enabled bit

     set @serverName = CAST(serverproperty('servername') as sysname)
     set @databaseName = db_name()  

     select @is_data_access_enabled = [is_data_access_enabled] 
     from   sys.servers
     where  name = @serverName
     and    is_data_access_enabled = 0

     if  
     (
             (@is_data_access_enabled is null) 
          or (@is_data_access_enabled = 0) 
      )    
      begin

         print 'Setting ServerOption:'
         print '
            Msg 7411, Level 16, State 1, Procedure sp_primarykeys, Line 10
                Server  is not configured for DATA ACCESS.
              '

         exec sp_serveroption 
                  @serverName 
                , 'data access'
                , 'true' --execute once  

end

exec sp_primarykeys
          @table_server = @serverName
        , @table_catalog = @databaseName 
        , @table_schema = 'dbo'
        , @table_name = 'customer'

Output:

sp_primary_keys

Explanation:

  • The sp_primarykeys SP is quite straightforward
  • But, the ‘data access’ server option needs to be turned on

 

Using INFORMATIONAL_SCHEMA



Syntax:

   --http://stackoverflow.com/questions/3930338/
   --     sql-server-get-table-primary-key-using-sql-query   
   SELECT 
              tblTableKey.CONSTRAINT_SCHEMA
            , tblTableKey.TABLE_NAME
            , tblTableKey.column_name
            , tblTableKey.ORDINAL_POSITION
   FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE tblTableKey
   WHERE  OBJECTPROPERTY(OBJECT_ID(tblTableKey.constraint_name)
                           , 'IsPrimaryKey') = 1
   AND    tblTableKey.CONSTRAINT_SCHEMA = 
   AND    tblTableKey.table_name = 
   ORDER  by tblTableKey.ORDINAL_POSITION asc
   ;

Sample:

   SELECT 
              tblTableKey.CONSTRAINT_SCHEMA
            , tblTableKey.TABLE_NAME
            , tblTableKey.column_name
            , tblTableKey.ORDINAL_POSITION
   FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE tblTableKey
   WHERE  OBJECTPROPERTY(OBJECT_ID(tblTableKey.constraint_name)
                         , 'IsPrimaryKey') = 1
   AND    tblTableKey.CONSTRAINT_SCHEMA = 'dbo'
   AND    tblTableKey.table_name = 'customer'
   ORDER  by tblTableKey.ORDINAL_POSITION asc
   ;

Table -- Informational_Schema

References:

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s