Check existance of primary key
Torgeir Punnerud
Does anybody know how to check for the existance of a primary key on a
table?
Thanks,
Torgeir
Predrag
select t.name as table_name,
c.name as pk_name
from sysobjects t
left join sysobjects c
on t.id = c.parent_obj
and c.xtype = 'PK'
where t.xtype = 'U'
order by t.name
If you only want to check for if a pk exists:
select * from sysobjects t
inner join sysobjects c
on t.id = c.parent_obj
and c.xtype = 'PK'
where t.xtype = 'U'
and t.name = @your_table
"Torgeir Punnerud" <torgeir....@bigfoot.com> wrote in message news:<2kPI7.108$B87....@news1.oke.nextra.no>...
BP Margolin
Microsoft strongly suggests not directly referencing system tables if at all
possible. The structure and content of the SQL Server system tables is
subject to change from version to version of SQL Server.
Microsoft supports the ANSI defined INFORMATION_SCHEMA views. Since the
INFORMATION_SCHEMA views are defined by the ANSI SQL committee, and not
Microsoft, one has a much greater probability that the INFORMATION_SCHEMA
views will always be backward compatible.
To do what you want, using the INFORMATION_SCHEMA views:
use pubs
if exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'authors'
AND CONSTRAINT_TYPE = 'PRIMARY KEY')
print 'The table has a primary key'
else
print 'The table does not have a primary key'
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Torgeir Punnerud" <torgeir....@bigfoot.com> wrote in message
news:2kPI7.108$B87....@news1.oke.nextra.no...
Paladin
**BLINK*BLINK**
Whoa. Well shave my head and slap me silly.
/Paladin skips off to do some reading.
P.S. Don't be too terribly harsh - such practices are fair when used in a
one-time use situation for general management purposes. Learning the views
seems to be a better way. They will be mine!
"BP Margolin" <bpm...@attglobal.net> wrote in message
news:3bf45...@news3.prserv.net...
Erland Sommarskog
> Microsoft strongly suggests not directly referencing system tables if at
> all possible. The structure and content of the SQL Server system tables
> is subject to change from version to version of SQL Server.
Well, BP is exagerating a bit. Books Online says in one place:
To obtain meta data, use system stored procedures, system functions, or
these system-supplied views only. Querying the system tables directly may
not provide accurate information if system tables are changed in future
releases.
But in another:
Reference of documented columns in system tables is permissible. However,
many of the columns in system tables are not documented. Applications
should not be written to query undocumented columns directly.
Applications should instead use any of these components to retrieve
information stored in the system tables:
In practice you have to learn the system tables in order to understand
the information_schema views. And while all information is in the system
tables, not everything is in information_schema.
In the end, which you pick is a matter of choice. Personally, I have
used the system tables for ten years, and I don't plan to give up.
--
Erland Sommarskog, Abaris AB
som...@algonet.se
SQL Server MVP
BP Margolin
One of the advantage of being a Microsoft SQL Server MVP is that we have
access to the SQL Server development team. I know, from information that
dates back quite a while ... before Erland became an MVP ... that there are
some on the Microsoft development team who wish, in future releases, to
completely remove access ... even SELECT access ... to the system tables.
Their desire to do this is to avoid the issues that come up from moving from
one release of SQL Server to another from users who have coded directly
against the system table. Now before anyone jumps up and down ... I have no
inside information as to whether this will ever happen. Nonetheless, the
fact that it is an "ultimate" design goal of some on the SQL Server
development team should give one pause re: code that references the system
tables when there are alternatives available.
As a general rule of thumb, the MVP's try to suggest the programming
practices recommended by the SQL Server development team. Because of various
NDA situations, we are not always at liberty to explain in detail. In this
case, I am not breaking any NDA, so I can post this.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Paladin" <m...@me.com> wrote in message
news:Ir2J7.45208$S4.41...@newsread1.prod.itd.earthlink.net...
Greg D. Moore (Strider)
> Paladin, Erland,
>
> One of the advantage of being a Microsoft SQL Server MVP is that we have
Been meaning to ask... how does one BECOME an MVP?
BP Margolin
Neil Pike once posted that he accepted monetary bribes :-)
Basically, posts to the Microsoft sponsored newsgroup are monitored by
Microsoft. Individuals who contribute posts of sufficient quality and
quantity are considered. The existing MVPs tend to have some input, but the
final decision is always that of Microsoft.
BTW, I also accept monetary bribes :-)
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Greg D. Moore (Strider)" <moo...@greenms.com> wrote in message
news:ROiJ7.64724$M21.13...@typhoon.nyroc.rr.com...
Neil Pike
Unfortunately no-one's ever given me any... :-(
> Basically, posts to the Microsoft sponsored newsgroup are monitored by
> Microsoft. Individuals who contribute posts of sufficient quality and
> quantity are considered. The existing MVPs tend to have some input, but the
> final decision is always that of Microsoft.
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq
BP Margolin
Gee, you mean the money I sent you was NOT instrumental in my becoming an
MVP !!
Hey then, I want a refund !!
:-)
BPM
"Neil Pike" <10057...@compuserve.com> wrote in message
news:VA.00000f7...@compuserve.com...
Greg D. Moore (Strider)
>
> Unfortunately no-one's ever given me any... :-(
I had a math teacher in high school who said he accepted bribes, but no one
had ever offered one high enough.
(Considering the wealth and lack of morals of some of my classmates I'm sure
a few tried. :-)
Neil Pike
Royce... ;-)
I wondered why the postman delivers> Gee, you mean the money I sent you was NOT
> MVP !!
>
> Hey then, I want a refund !!
Neil Pike MVP/MCSE. Protech Computing Ltd
Erland Sommarskog
> One of the advantage of being a Microsoft SQL Server MVP is that we have
> access to the SQL Server development team. I know, from information that
> dates back quite a while ... before Erland became an MVP ... that there
> are some on the Microsoft development team who wish, in future releases,
> to completely remove access ... even SELECT access ... to the system
> tables. Their desire to do this is to avoid the issues that come up from
> moving from one release of SQL Server to another from users who have
> coded directly against the system table.
Sounds like throwing out the baby with the bathtub.
Possibly in some future release I will move over to use INFORMATION_SCHEMA,
but I really hope that there will be a lowercase version of them then.
And that they will actually be complete.
The way the situation is today, you still need to know the system tables
because 1) not everything is in INFORMATION_SCHEMA. 2) to understand what
actually is there, you need to know the system tables.
Of course, some people at Microsoft may have a design goal. Then again,
I also suppose that they listen to users.
oj
to *truly* understand what's going on, one must know system tables. it's a
good idea to recommend *newbie* to hit information_schema views as to
protect them from accidentally destroy their server. by the way, who can
guarantee that there is no change in information_schema in future release.
things change just system objects. a dba just gotta know that and work with
the provided tools.
-oj
"Erland Sommarskog" <som...@algonet.se> wrote in message
news:Xns915F766F...@127.0.0.1...
Erland Sommarskog
>By the way, who can >guarantee that there is no change in information_schema
>in future release. things change just system objects.
To be fair, INFORMATION_SCHEMA are ANSI standard, so they are far
less likely to change than the system tables. What could changes is
the semantics of some columns. There are some columns in
INFORMATION_SCHEMA that does not include what they are supposed include,
because that information is not available in the system tables. That
is something which could be fixed in a future release.
--
Erland Sommarskog, Stockholm, som...@algonet.se
BP Margolin
In addition to what Erland has posted ... and he is absolutely correct that
the advantage of the INFORMATION_SCHEMA views is that they are defined by
the ANSI standard rather than Microsoft ...
I wouldn't necessary agree that it is imperative that a SQL Server DBA know
that much about the system tables. Certainly a "development" DBA ... aka a
System Architect ... really doesn't need to know that much about the system
tables IMHO. The design of a database ... tables, proper indexing, and the
such ... is independent of how SQL Server stores this information. I
certainly accept that a SQL Server System Architect should understand how
SQL Server deals with indexes ... clustered and non-clustered, for example
... but I don't think it is relevant that the System Architect know, for
example, that an indid value of zero means that the table is a heap, while
an indid value of one means that the index is clustered.
I **might** want an operational DBA to know a little about the system tables
... but even there, with the advent of SQL Server 7.0 and 2000, and the
increased stability of these releases, I'm not sure it is really critical.
Certainly, on releases of SQL Server prior to version 7.0, one should be
able to know how to reset a database in suspect status. But, and I just
checked, SQL Server 7.0 / 2000 supports the sp_resetstatus system stored
procedure. Frankly I don't remember if this existed before SQL Server 7.0.
oj, why do you believe that a knowledge of the system tables is a critical
need for a SQL Server DBA?
BPM
"oj" <nospam...@home.com> wrote in message
news:vDmK7.48758$XJ4.28...@news1.sttln1.wa.home.com...
Erland Sommarskog
> I wouldn't necessary agree that it is imperative that a SQL Server DBA know
> that much about the system tables. Certainly a "development" DBA ... aka a
> System Architect ... really doesn't need to know that much about the system
> tables IMHO. The design of a database ... tables, proper indexing, and the
> such ... is independent of how SQL Server stores this information.
Yes, were I only to design our back-office system, I would have no interest
in the system tables.
The area where you have use of them - or INFORMATION_SCHEMA - is for
tools and utilities. My aba_lockinfo is one example. The quite elaborate
build tool that we use internally is another example. In this I query
sysindexes to find out whether the indexes in the file are the same as
the existing ones for example (so I don't rebuild them when there is
no need to). Another example is where I query sysfereneces and other
columns to find foreign key references to one table, so that I can move
then from the old table to the new version of the table.
Incidently, the first example here, aba_lockinfo reads sysprocesses and
syslockinfo. It is a procedure that I expect to adapt to each new version
of SQL Server. There are two different files; one for SQL 6.5 and one for
SQL7/2000. The difference between SQL7 and SQL2000 were not bigger than
it could be handled dynamically. But I see that as mere chance.
On the other hand, the code that queries the catalog tables are somewhat
more stable. Somewhat that is. The code for moving foreign keys is the
same from SQL6.0 to SQL2000. The code for querying sysindexes is different
for all of SQL6.5, SQL7 and SQL2000.
There is another area where I have used the system tables. When we
migrated the database to SQL2000, I wanted to know how the table
changed in size to identify clustered indexes that needed modofication.
This was an exercise with sysindexes, and combining results from
sysindexes on two different servers to see the effect. And here you
are in an area which at least touches the one of the system architect.
So the bottom line is that just as you can program SQL without knowing
about derived tables, you can work on SQL Server without knowing the
system tables, but in both cases you are missing out on useful ways
to solve your problems.