killer query - limit usage ?

Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>
That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:

>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>
|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.
|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>
|||Not sure what you mean by option2 but if you place the filegroup or database
in read only mode sql server will not take out any locks when you read it
since it knows no one can change it while you read. If no one is making
changes you can also use the READ UNCOMMITED isolation level to achieve the
same end results.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:O5L%23RnkuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> very good point Andrew, sorry for being a little dim.
> i read something about a READ ONLY option 2 which is supposed to speed up
> queries.
> Thanks for your time, great help
> Scott
>

Kill

I need to let a third party security app run a script as a
system admin to drop and recreate a database. Before this
will run, of course I need to make sure all connections to
that database are dropped.
Is there a command that will kill all connections to a
database?Sometimes you just have to trace other programs that can do this. I traced
what happens when you disconnect a database and someone is using it.
select spid from master..sysprocesses where dbid=db_id('<database name>')
Then that spid result is fed to a kill statement.
Should warn you that this is a tricky thing that you are doing. Certain
kinds of connections, such as those with SQL Query Analyzer and Enterprise
Manager, do not drop very easily. Sometimes connections keep going. A
drastic step might be to use a net stop/net start to restart MSSQLserver.
That will certainly free up all the connections, though the database might
go into recovery.
But no, if the Clear connection button on the Detach Database function in
SQL EM doesn't call a command, I doubt you are going to find one.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||Add these lines to the top of the script.
ALTER DATABASE 'MyDBName' SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE 'MyDBName' SET ONLINE
GO
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||I know that I've seen a stored procedure on the net that will kill all
user connections. Try doing a search in google for something like "sp
kill all users" without the quotes.
Aaron
Andy Svendsen wrote:
> Sometimes you just have to trace other programs that can do this. I traced
> what happens when you disconnect a database and someone is using it.
> select spid from master..sysprocesses where dbid=db_id('<database name>')
> Then that spid result is fed to a kill statement.
> Should warn you that this is a tricky thing that you are doing. Certain
> kinds of connections, such as those with SQL Query Analyzer and Enterprise
> Manager, do not drop very easily. Sometimes connections keep going. A
> drastic step might be to use a net stop/net start to restart MSSQLserver.
> That will certainly free up all the connections, though the database might
> go into recovery.
> But no, if the Clear connection button on the Detach Database function in
> SQL EM doesn't call a command, I doubt you are going to find one.
>sql

keyword / phrase searching

I have to do an app that will contain a lot of string data that will be
keywords and phrases and I will need a very fast search of that material.
Does SQL Server 2005 have any tools that are geared to this kind of search.
I don't think it has to be as fast as google but probably faster than where
clauses using "like" and "in".
Thanks,
TCheck out the Full Text Search features in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uaSeL83tHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I have to do an app that will contain a lot of string data that will be
>keywords and phrases and I will need a very fast search of that material.
>Does SQL Server 2005 have any tools that are geared to this kind of search.
>I don't think it has to be as fast as google but probably faster than where
>clauses using "like" and "in".
> Thanks,
> T
>

keyword / phrase searching

I have to do an app that will contain a lot of string data that will be
keywords and phrases and I will need a very fast search of that material.
Does SQL Server 2005 have any tools that are geared to this kind of search.
I don't think it has to be as fast as google but probably faster than where
clauses using "like" and "in".
Thanks,
T
Check out the Full Text Search features in BooksOnLine.
Andrew J. Kelly SQL MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uaSeL83tHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I have to do an app that will contain a lot of string data that will be
>keywords and phrases and I will need a very fast search of that material.
>Does SQL Server 2005 have any tools that are geared to this kind of search.
>I don't think it has to be as fast as google but probably faster than where
>clauses using "like" and "in".
> Thanks,
> T
>

keyword / phrase searching

I have to do an app that will contain a lot of string data that will be
keywords and phrases and I will need a very fast search of that material.
Does SQL Server 2005 have any tools that are geared to this kind of search.
I don't think it has to be as fast as google but probably faster than where
clauses using "like" and "in".
Thanks,
TCheck out the Full Text Search features in BooksOnLine.
Andrew J. Kelly SQL MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uaSeL83tHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I have to do an app that will contain a lot of string data that will be
>keywords and phrases and I will need a very fast search of that material.
>Does SQL Server 2005 have any tools that are geared to this kind of search.
>I don't think it has to be as fast as google but probably faster than where
>clauses using "like" and "in".
> Thanks,
> T
>

key violation, general sql error, connectin busy with another hstmt

We are getting this error periodically in a large app we are converting
from Access to SQL Server 2000. It uses BDE and ODBC for data access
and TTable/TQuery as well as TwwTable/TwwQuery components (from
woll2woll) under Delphi 6. It appears to happen when we are executing
TQuery.Open.
From what I've read, this is caused by ODBC not completing a result set
before processing another request. Which might be caused by having
queries run in multiple threads, but this is not the case."Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> We are getting this error periodically in a large app we are
> converting
> from Access to SQL Server 2000. It uses BDE and ODBC for data
> access
> and TTable/TQuery as well as TwwTable/TwwQuery components (from
> woll2woll) under Delphi 6. It appears to happen when we are
> executing
> TQuery.Open.
> From what I've read, this is caused by ODBC not completing a
> result set
> before processing another request. Which might be caused by
> having
> queries run in multiple threads, but this is not the case.
Basically, you can't have two open (SELECT) statements on the
same connection. For example,
Open query 1
Get some info from a record
Use that to set params in query 2
Open query 2 -- This will fail
This assumes both statements are attached to the same hDBC. It
is a result of using a client-side cursor for the hStmt's.
You'll have to use server-side cursors for the statements to make
this work. See MSDN.
This is one way to force a server-side cursor:
SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER) SQL_SCROLLABLE, 0 );
Good luck,
- Arnie|||So you can never have 2 open queries? I do that all the time, in one
thread. Maybe I'm not understanding. For example, this code works
fine, which creates 101 open queries:
--
procedure TForm1.ManyQueriesClick(Sender: TObject);
var qs : array[0..100] of TQuery ;
var q : Tquery;
var i : Integer;
begin
for i := 0 to 100 do begin
qs[i] := TQuery.Create(self);
q := qs[i];
q.databasename := 'WM';
q.SQL.Clear;
q.SQL.Add('SELECT * FROM CUSTOMERFIELDS');
statusbar1.SimpleText := 'Query ' + IntToStr(i);
q.active := true;
end;
for i := 0 to 100 do
qs[i].Free;
end;|||Arnie wrote:

> "Doug Stephens" <dougstephens@.rogers.com> wrote in message
> news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> Basically, you can't have two open (SELECT) statements on the same
> connection. For example,
> Open query 1
> Get some info from a record
> Use that to set params in query 2
> Open query 2 -- This will fail
> This assumes both statements are attached to the same hDBC. It is a
> result of using a client-side cursor for the hStmt's. You'll have to
> use server-side cursors for the statements to make this work. See
> MSDN.
> This is one way to force a server-side cursor:
> SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)
> SQL_SCROLLABLE, 0 );
>
> Good luck,
> - Arnie
How can I run SQLSetStmtAttr from Delphi?|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:uVMztRHGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> How can I run SQLSetStmtAttr from Delphi?
You can't. It's an ODBC statement. The equivalent in Delphi
would be CursorLocation := clUseServer for TADO components.
Sorry, but I've totally forgotten about the BDE. It may use
server-side cursors by default.
The problem occurs in ODBC when nesting queries that use the same
connection handle. Open q1, read some field values, use these to
set params in q2 and then open q2. This last open will cause the
error. In theory, SQL Server 2005 has 'fixed' this 'feature',
though I haven't tried it yet.
- Arnie|||I found a way to call this function in ODBC32.DLL from Delphi but what
is the hstmt?|||We have same problem with 2005.|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:e3UtnUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> We have same problem with 2005.
What DB objects are you using with 2005?
- Arnie|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:OfWhHUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
>I found a way to call this function in ODBC32.DLL from Delphi
>but what
> is the hstmt?
As far as I know, you'd have to be using ODBC directly rather
than the BDE. hStmt is the ODBC statement handle.
- Arnie|||Uh, tables and indexes. No stored procs. Is that what you mean?

key violation, general sql error, connectin busy with another hstmt

We are getting this error periodically in a large app we are converting
from Access to SQL Server 2000. It uses BDE and ODBC for data access
and TTable/TQuery as well as TwwTable/TwwQuery components (from
woll2woll) under Delphi 6. It appears to happen when we are executing
TQuery.Open.
From what I've read, this is caused by ODBC not completing a result set
before processing another request. Which might be caused by having
queries run in multiple threads, but this is not the case.
"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> We are getting this error periodically in a large app we are
> converting
> from Access to SQL Server 2000. It uses BDE and ODBC for data
> access
> and TTable/TQuery as well as TwwTable/TwwQuery components (from
> woll2woll) under Delphi 6. It appears to happen when we are
> executing
> TQuery.Open.
> From what I've read, this is caused by ODBC not completing a
> result set
> before processing another request. Which might be caused by
> having
> queries run in multiple threads, but this is not the case.
Basically, you can't have two open (SELECT) statements on the
same connection. For example,
Open query 1
Get some info from a record
Use that to set params in query 2
Open query 2 -- This will fail
This assumes both statements are attached to the same hDBC. It
is a result of using a client-side cursor for the hStmt's.
You'll have to use server-side cursors for the statements to make
this work. See MSDN.
This is one way to force a server-side cursor:
SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER) SQL_SCROLLABLE, 0 );
Good luck,
- Arnie
|||So you can never have 2 open queries? I do that all the time, in one
thread. Maybe I'm not understanding. For example, this code works
fine, which creates 101 open queries:
procedure TForm1.ManyQueriesClick(Sender: TObject);
var qs : array[0..100] of TQuery ;
var q : Tquery;
var i : Integer;
begin
for i := 0 to 100 do begin
qs[i] := TQuery.Create(self);
q := qs[i];
q.databasename := 'WM';
q.SQL.Clear;
q.SQL.Add('SELECT * FROM CUSTOMERFIELDS');
statusbar1.SimpleText := 'Query ' + IntToStr(i);
q.active := true;
end;
for i := 0 to 100 do
qs[i].Free;
end;
|||Arnie wrote:

> "Doug Stephens" <dougstephens@.rogers.com> wrote in message
> news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> Basically, you can't have two open (SELECT) statements on the same
> connection. For example,
> Open query 1
> Get some info from a record
> Use that to set params in query 2
> Open query 2 -- This will fail
> This assumes both statements are attached to the same hDBC. It is a
> result of using a client-side cursor for the hStmt's. You'll have to
> use server-side cursors for the statements to make this work. See
> MSDN.
> This is one way to force a server-side cursor:
> SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)
> SQL_SCROLLABLE, 0 );
>
> Good luck,
> - Arnie
How can I run SQLSetStmtAttr from Delphi?
|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:uVMztRHGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> How can I run SQLSetStmtAttr from Delphi?
You can't. It's an ODBC statement. The equivalent in Delphi
would be CursorLocation := clUseServer for TADO components.
Sorry, but I've totally forgotten about the BDE. It may use
server-side cursors by default.
The problem occurs in ODBC when nesting queries that use the same
connection handle. Open q1, read some field values, use these to
set params in q2 and then open q2. This last open will cause the
error. In theory, SQL Server 2005 has 'fixed' this 'feature',
though I haven't tried it yet.
- Arnie
|||I found a way to call this function in ODBC32.DLL from Delphi but what
is the hstmt?
|||We have same problem with 2005.
|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:e3UtnUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> We have same problem with 2005.
What DB objects are you using with 2005?
- Arnie
|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:OfWhHUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
>I found a way to call this function in ODBC32.DLL from Delphi
>but what
> is the hstmt?
As far as I know, you'd have to be using ODBC directly rather
than the BDE. hStmt is the ODBC statement handle.
- Arnie
|||Uh, tables and indexes. No stored procs. Is that what you mean?

Key locks and Deadlocks

I don't know if this is the best place to post this, if not, please
advise...
I have a SQL 2000 production db that the users of a VB 6.0 app occasionally
see deadlock errors. Like so many times, it's not reproducible, but it
happens more when they are really busy (Thanks, Murf!).
In running the performance Monitor, I see that a significant number (over
10,000) key locks get generated during some queries. I have run the Sql
Analyyzer and cannot find any specific transaction or sequence of events
that results in the deadlock.
My theory is the deadlocking is coming from page locks, and each user
contends just a little too much for some pages.
The other possibility is that the key locks, being so numerous could be
blocking and causing the deadlock victims transaction to roll back.
So, my question is two-fold:
1) can Key lock contention cause deadlocks? If so, how do I reduce keylock
use?
2) Is there a way to control or force row locking when using ADO, VB6 style;
specifically when using the .Update method on an ADO recordset (I realize I
could re-write all the updates to be via striaght SQL, so I could insert my
own hints regarding rowlocking, and yes, I already have a post in
public.data.ado message group on this).
Thanks!
Steve
Steve Byrne wrote:
> I don't know if this is the best place to post this, if not, please
> advise...
> I have a SQL 2000 production db that the users of a VB 6.0 app
> occasionally see deadlock errors. Like so many times, it's not
> reproducible, but it happens more when they are really busy (Thanks,
> Murf!).
> In running the performance Monitor, I see that a significant number
> (over 10,000) key locks get generated during some queries. I have run
> the Sql Analyyzer and cannot find any specific transaction or
> sequence of events that results in the deadlock.
> My theory is the deadlocking is coming from page locks, and each user
> contends just a little too much for some pages.
> The other possibility is that the key locks, being so numerous could
> be blocking and causing the deadlock victims transaction to roll back.
> So, my question is two-fold:
> 1) can Key lock contention cause deadlocks? If so, how do I reduce
> keylock use?
> 2) Is there a way to control or force row locking when using ADO, VB6
> style; specifically when using the .Update method on an ADO recordset
> (I realize I could re-write all the updates to be via striaght SQL,
> so I could insert my own hints regarding rowlocking, and yes, I
> already have a post in public.data.ado message group on this).
> Thanks!
> Steve
See this page about identifying and resolving deadlocks:
http://support.microsoft.com/?kbid=832524
The first step is to see what transactions are responsible for the
deadlocks. To resolve the deadlock, you should:
- Make sure all transactions involved are fully optimized
- Access objects in the same order in all transactions
- Keep the transactions short - (fetch all result set data immediately
and avoid leaving locks on the server)
- Use the lowest level isolation level possible (READ COMMITTED, READ
UNCOMMITTED, REPEATABLE READ, and then SERIALIZABLE)
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Also try with (nolock) for dirty reads.
Helps greatly on a heavy OLTP server and you don't need "to the second"
accuracy.
Mike
"Steve Byrne" <steveb@.ssninc.com> wrote in message
news:OJ9SZn9sFHA.2348@.tk2msftngp13.phx.gbl...
I don't know if this is the best place to post this, if not, please
advise...
I have a SQL 2000 production db that the users of a VB 6.0 app occasionally
see deadlock errors. Like so many times, it's not reproducible, but it
happens more when they are really busy (Thanks, Murf!).
In running the performance Monitor, I see that a significant number (over
10,000) key locks get generated during some queries. I have run the Sql
Analyyzer and cannot find any specific transaction or sequence of events
that results in the deadlock.
My theory is the deadlocking is coming from page locks, and each user
contends just a little too much for some pages.
The other possibility is that the key locks, being so numerous could be
blocking and causing the deadlock victims transaction to roll back.
So, my question is two-fold:
1) can Key lock contention cause deadlocks? If so, how do I reduce keylock
use?
2) Is there a way to control or force row locking when using ADO, VB6 style;
specifically when using the .Update method on an ADO recordset (I realize I
could re-write all the updates to be via striaght SQL, so I could insert my
own hints regarding rowlocking, and yes, I already have a post in
public.data.ado message group on this).
Thanks!
Steve
|||Mike Perino wrote:
> Also try with (nolock) for dirty reads.
> Helps greatly on a heavy OLTP server and you don't need "to the
> second" accuracy.
Or you can handle reading data that exists now, but is rolled back
afterwards. Essentially, data that never existed. I agree, though, that
any application that does not require accurate data be returned for a
query should consider using this locking hint. For example, a query that
returns estimated totals sales for the month.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Key Exists?

What's the best SQL statement to use to detect if a Key Exists in a
particular table?
I had been using SQLDMO within a VB app to access possible keys in the table
and then find if one matches what I'm looking for:
For X = 1 To SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableNam
e)
.Keys.Count
If Trim(UCase(KeyName)) = UCase(Trim(SQLDMOConnection.Databases(UCase
(DatabaseName)).Tables(TableName).Keys(X).Name)) Then
KeyExists = True
Exit For
End If
Next X
I've decided not to do this, and instead use SQL statements to get the
information.
So I need some way of traversing keys on a table and see the names and find
a
match to thename I'm looking for.
How's the best way to do this?Okay. I've got some of what I need.
I know that I can use OBJECTPROPERTY(OBJECT_ID('tablename.fieldname'),
'IsPrimaryKey') to find out if a field is a key. Can I specify table/field
in the OBJECT_ID call?
Also, before I do this, I'd like to check the table to see if it has a
primary key.
So...
OBJECTPROPERTY(OBJECT_ID('tablename'),'T
ableHasPrimaryKey')
Now those are elements of what I need.
What are the full statements to make it work?
E. coli Happens.|||It would sure be nice if someone could take the pieces and put them together
into a sql statement or statements that I can use.
Les Stockton wrote:
>Okay. I've got some of what I need.
>I know that I can use OBJECTPROPERTY(OBJECT_ID('tablename.fieldname'),
>'IsPrimaryKey') to find out if a field is a key. Can I specify table/field
>in the OBJECT_ID call?
>Also, before I do this, I'd like to check the table to see if it has a
>primary key.
>So...
> OBJECTPROPERTY(OBJECT_ID('tablename'),'T
ableHasPrimaryKey')
>Now those are elements of what I need.
>What are the full statements to make it work?
>
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||This would list all the tables in the current database that have a primary
key, and the name of the primary key on the table.
SELECT s1.[name] AS "Table", s2.[name] AS "Key"
FROM sysobjects s1 INNER JOIN sysobjects s2
ON s2.[parent_obj]=s1.[id]
WHERE s2.[xtype]='PK'
You could add a WHERE clause to look at a specific table and an aggregate
COUNT to get a 0 or 1 returned from the statement.
SELECT COUNT(*)
FROM sysobjects s1 INNER JOIN sysobjects s2
ON s2.[parent_obj]=s1.[id]
WHERE s2.[xtype]='PK' AND s1.[name]='table_name'
Returns 1 if table_name has a primary key, and zero if it doesn't.
"HockeyFan" wrote:

> What's the best SQL statement to use to detect if a Key Exists in a
> particular table?
> I had been using SQLDMO within a VB app to access possible keys in the tab
le
> and then find if one matches what I'm looking for:
> For X = 1 To SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableN
ame)
> ..Keys.Count
> If Trim(UCase(KeyName)) = UCase(Trim(SQLDMOConnection.Databases(UCase
> (DatabaseName)).Tables(TableName).Keys(X).Name)) Then
> KeyExists = True
> Exit For
> End If
> Next X
> I've decided not to do this, and instead use SQL statements to get the
> information.
> So I need some way of traversing keys on a table and see the names and fin
d a
> match to thename I'm looking for.
> How's the best way to do this?
>|||I did.
How much of my post did you read?
Les Stockton via webservertalk.com wrote:
> It would sure be nice if someone could take the pieces and put them togeth
er
> into a sql statement or statements that I can use.
> Les Stockton wrote:
>
>