Transact SQL – Constraint – Primary Key

There are a couple of approaches one can use to get the primary key for a table.

Here are some of those ways:

  • sp_pkeys
  • sp_primarykeys
  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS

sp_pkeys

Syntax:

exec sp_pkeys
@table_name = [table-name]
, @table_owner = [schema-name]
go

Sample:

exec sp_pkeys
@table_name = 'DimEmployee'
, @table_owner = 'dbo'
go

Output:

sp_pkeys

sp_primarykeys

Though sp_primarykeys was added to gain insight into remote data sources, you can use it it to query your local data source, as well.

Btw, to get foreign key data on remote data sources, please refer to sp_foreignkeys (Transact-SQL) – http://technet.microsoft.com/en-us/library/ms187337.aspx.

Syntax:

exec sp_primarykeys
table_server = [SQLInstanceName]
, table_catalog = [DatabaseName]
, @table_name = [tableName]
, @table_owner = [schemaName]
go

Sample:


use [AdventureWorksDW2008R2]
go

declare @serverName sysname
declare @databaseName sysname

--set server name to current SQL Instance
set @serverName = cast(SERVERPROPERTY('servername') as sysname)

--set database name to current database
set @databaseName = DB_NAME()

exec sp_primarykeys
@table_server = @servername
, @table_catalog = @databaseName
, @table_name = 'DimEmployee'
, @table_schema = 'dbo'
go

Output:

You might likely get a subtle error:


Msg 7411, Level 16, State 1, Procedure sp_primarykeys, Line 10
Server server-name is not configured for DATA ACCESS.

And, to correct try:

  • Enabling Data Access on the Data Source
  • The Data Source in this case is any registered OLE-DB Provider and so you can query the local or a remote data source

declare @serverName sysname
declare @isDataAccessEnabled bit

--set servername to local server
set @serverName = cast(SERVERPROPERTY('servername') as sysname)

--get data access enabled flag
select
@isDataAccessEnabled = tblServer.is_data_access_enabled
from sys.servers tblServer
where tblServer.name = @serverName

print '@isDataAccessEnabled :' + cast(@isDataAccessEnabled as sysname)

--if data access to sql server instance is disabled, please enable
if (@isDataAccessEnabled = 0)
begin

print 'Data Access on ' + @serverName + ' is currently disabled'

print 'Allowing Data access ' + @serverName + ' ...'

exec sp_serveroption @serverName , 'data access', 'true'

print 'Data Access on ' + @serverName + ' is now enabled'

end

 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Syntax:


select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblConstraint
where tblConstraint.CONSTRAINT_TYPE = 'PRIMARY KEY'
and     tblConstraint.CONSTRAINT_CATALOG = [database-Name]
and     tblConstraint.TABLE_SCHEMA = [schema-name]
and     tblConstraint.TABLE_NAME = [table-name]
go

Sample:


select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblConstraint
where tblConstraint.CONSTRAINT_TYPE = 'PRIMARY KEY'
and     tblConstraint.CONSTRAINT_CATALOG = @databaseName
and     tblConstraint.TABLE_SCHEMA = 'dbo'
and     tblConstraint.TABLE_NAME = 'DimEmployee'
go

Output:

informationSchemaTable

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