Help Identifying Full table scans with objectname

Hello
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon

Help for Newbie!

I have a table with 10 records: 10, 9, 8, 7 ... and so on. I want to SELECT the top 3 records starting with the second record on the list (which in this case is 9.) So the result should be 9, 8, and 7. What would be the correct SQL statement for this?

I tried "SELECT TOP 3 * FROM tableXXX WHERE (FieldID<>(SELECT TOP 1 * FROM tableXXX ORDER BY FieldID DESC) ORDER BY FieldID DESC" but sure as heck it didn't work. Can anyone help?drop the * on the 2nd select and only qry for the pkval :

SELECT Top 3 *
FROM Contacts
WHERE idCont <> (SELECT Top 1 idCont FROM Contacts)|||Thank you, Mike. It works beautifully! The complete statement is:

SELECT Top 3 *
FROM tbl_XXX
WHERE (idXXX <>(SELECT Top 1 idXXX FROM tbl_XXX ORDER BY idXXX DESC))
ORDER BY idXXX DESC

Again, thank you. You are great help.

Help for a real newbie

A problem so simple I'm a little embarrassed to post it... I am setting up a trigger in which I want to, among other things, record the network ID of the user who performs the action that triggers the trigger. I know there is a variable or function out there to pull that information, but I can't find it, and have tried every variation of "login logname usr_id network_id....." I can find.

So first.... could somebody please tell me the variable or function that will return that piece of information. And second, if you also could point me to a good place, either in print or on line, to look up that kind of information, I would be most grateful.

Thanks.

Hello Leslie. The first thing to remember, there is no stupid question (well almost never )

The second thing I will point you to is the Downloadable version of the "BOL" (Books On Line).

It can be found here. http://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=SQL+BOL If you are using SQL 2005 then it will be the 4th link down. You should also be able to access this by hitting F1 in your Enterprise Manager or inside of SQL Studio. Then switch over to the Index.

Then remmeber that most variables in SQL that are global either scoped to the whole server or the current connection will usually begin with a @.@.. Typing the @.@. in the index will get you in the near vicinity.

I personally found the @.@.PROCID which

Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @.@.PROCID cannot be specified in CLR modules or the in-process data access provider.

and @.@.SPID

Returns the session ID of the current user process.

Try both of those and see if one of those does what you are looking for.

Hope that helps. If you have further questions feel free to ask.

|||

Hi Leslie,

I think you're after the SYSTEM_USER function, which will return the windows login name in the form of ADomain\AUser (if the current user has used a trusted win connection of course).

Cheers,

Rob

Help finding record pairs

I'm trying to find away to match up two rows into a single event.
PK Name Event Type
1 Foo Open Foreground
8 Foo Close Foreground
16 Bar Open Foreground
18 Bar Open Background
22 Bar Close Background
23 Bar Cose Foreground
I need to know that
1 is paired with 8
16 is paired with 23
18 is paired with 22
Any help would be appreciatedPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
This looks like a common design error. Time is based on durations
(Zeno, Einstein, et al) and not on split facts like you are showing.
Also, your data element names are much too vague for any data model.
The kludge to repair the design flaw will involve self-joins and
aggregations. It has been posted several times in various forms.
I think that you wanted something like this in the first place, instead
of wasting insane amounts of time constructing the proper design from
scratch everytime you use it.
CREATE TABLE Events
(event_name CHAR(3) NOT NULL,
open_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
close_time DATETIME, --null means still open
CHECK (open_time < close_time),
event_type CHAR(10) NOT NULL
CHECK (event_type IN ('Foreground', 'Background')),
PRIMARY KEY (event_name, open_time));|||"lee_mre@..no-spam.yahoo.com" <lee_mre@.yahoo.com> wrote in message
news:1134010863.875294.231700@.o13g2000cwo.googlegroups.com...
> I'm trying to find away to match up two rows into a single event.
>
> PK Name Event Type
> 1 Foo Open Foreground
> 8 Foo Close Foreground
> 16 Bar Open Foreground
> 18 Bar Open Background
> 22 Bar Close Background
> 23 Bar Cose Foreground
>
> I need to know that
> 1 is paired with 8
> 16 is paired with 23
> 18 is paired with 22
> Any help would be appreciated
>
create table T
(
PK int primary key,
Name varchar(5),
Event varchar(5),
Type varchar(20)
)
insert into T(PK,Name,Event,Type)
select 1, 'Foo', 'Open', 'Foreground'
union all
select 8, 'Foo', 'Close', 'Foreground'
union all
select 16, 'Bar', 'Open', 'Foreground'
union all
select 18, 'Bar', 'Open', 'Background'
union all
select 22, 'Bar', 'Close', 'Background'
union all
select 23, 'Bar', 'Close', 'Foreground'
select
o.pk Opened, c.pk Closed
from
T o
join T c
on o.Name = c.Name
and o.Event = 'Open'
and c.Event = 'Close'
and o.Type = c.Type
--BUT I suspect that you really want to match each open event with the
"next" close event of the same type. Assuming that "next" means next ORDER
BY PK.
select
o.pk Opened,
(select min(pk)
from T
where Event = 'Close'
and Type = o.Type
and pk > o.pk) Closed
from T o
where Event = 'Open'
--David|||On 7 Dec 2005 19:50:22 -0800, --CELKO-- wrote:

> CREATE TABLE Events
> (event_name CHAR(3) NOT NULL,
> open_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> close_time DATETIME, --null means still open
> CHECK (open_time < close_time),
> event_type CHAR(10) NOT NULL
> CHECK (event_type IN ('Foreground', 'Background')),
> PRIMARY KEY (event_name, open_time));
Question ... how will the check constraint work when close_time is null on
an insert? Perhaps it should be
CHECK (NOT open_time >= close_time)
?|||On Thu, 8 Dec 2005 12:07:47 -0500, Ross Presser wrote:

>On 7 Dec 2005 19:50:22 -0800, --CELKO-- wrote:
>
>Question ... how will the check constraint work when close_time is null on
>an insert? Perhaps it should be
> CHECK (NOT open_time >= close_time)
>?
Hi Ross,
Not necessary. If close_time is NULL and open_time is '2005-12-10', then
the CHECK constraint suggested by Celko reads
CHECK (open_time < close_time)
after substitution
CHECK ('2005-12-10' < NULL)
which evaluates to
CHECK (Unknown)
A major difference between a logic test in a WHERE and a logic test in a
constraint is that a WHERE includes only rows if the test evaluates to
True (i.e. False and Unknown are both rejected), but a constraint
accepts rows if the test evaluates to either True or False (or, to
paraphrase the ANSI standard, the constraint must not evaluate to
False).
The row with NULL close_time will be allowed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||
Hugo Kornelis wrote:
<snip>

>A major difference between a logic test in a WHERE and a logic test in a
>constraint is that a WHERE includes only rows if the test evaluates to
>True (i.e. False and Unknown are both rejected), but a constraint
>accepts rows if the test evaluates to either True or False (or, to
>
I think you meant to write "... if the test evaluates to either True or
Unknown ..."
SK

>paraphrase the ANSI standard, the constraint must not evaluate to
>False).
>
>|||On Sat, 10 Dec 2005 20:16:22 -0500, Steve Kass wrote:

>
>Hugo Kornelis wrote:
><snip>
>
>I think you meant to write "... if the test evaluates to either True or
>Unknown ..."
Hi Steve,
You're right. I obviously typed to fast and forgot to check before
posting.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Help creating a DELETE trigger.

I need to create a trigger that will run whenever a record is deleted. I
want the trigger to delete records from table2 where the value in the ID
field matches the value in the ID field of the record being deleted from
table1.
I'm new to triggers and am not sure how to set this up.
Thanks,
JohnTry,
create trigger tr_table1_del on table1
for delete
as
set nocount on
if exists(select * from deleted as d inner join table2 as t on d.[id] = t[id])
begin
delete table2
where exists(select * from deleted ad d where d.[id] = table2.[id])
if @.@.error != 0
begin
rollback transaction
raiserror('Error deleting rows in table2.', 16, 1)
return
end
end
go
AMB
"John Piotrowski" wrote:

> I need to create a trigger that will run whenever a record is deleted. I
> want the trigger to delete records from table2 where the value in the ID
> field matches the value in the ID field of the record being deleted from
> table1.
> I'm new to triggers and am not sure how to set this up.
> Thanks,
>
> John

help create query against two tables

stepdefinition has
steptype
flowid
stepid

task has
taskid
flowid
stepid

flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.

well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.

select flowid, stepid from stepdefinition where steptype = -3

Now, I want to find all taskids in task for each flowid/stepid combination

here's a visual

http://www.filecabin.com/up1/1144249279-task.gifselect taskid from task
where stepID+flowID in
(select stepID+flowID from stepdefinition where steptype = -3)
order by taskid

works|||select t.taskid, t.stepID, t.flowID
from task as t
join stepdefinition as s
on ( t.stepID = s.stepID
and t.flowID = s.flowID )
where s.steptype = -3

HELP - insert record SQLExpress database

Hi! i ask you some help..I should build a simple INSERT FORM in a SQLExpress database..

but clicking ADD I have this error :

Incorrect syntax near '='.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near '='.

Source Error:

Line 38: conDatabase.Open()Line 39:Line 40: cmdInsert.ExecuteNonQuery()Line 41: Line 42: conDatabase.Close()

some solution?

the source code:

<%

@.PageLanguage="VB"Debug="true"%>

<%

@.ImportNamespace="System.Data"%>

<%

@.ImportNamespace="System.Data.SqlClient"%>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">Sub Button_clic(ByVal sAsObject,ByVal eAs EventArgs)Dim conDatabaseAs SqlConnectionDim strInsertAsStringDim cmdInsertAs SqlCommandDim myExecuteQueryAsStringDim myExecuteCmdAs SqlCommand

conDatabase =

New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\tesi\App_Data\database.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

strInsert =

"Insert pubblicazioni (Nome, Cognome, Titolo) Values = (@.Nome, @.Cognome, @.Titolo) "

cmdInsert =

New SqlCommand(strInsert, conDatabase)

cmdInsert.Parameters.Add(

"@.Nome", txtNome.Text)

cmdInsert.Parameters.Add(

"@.Cognome", txtCognome.Text)

cmdInsert.Parameters.Add(

"@.Titolo", txtTitolo.Text)

conDatabase.Open()

cmdInsert.ExecuteNonQuery()

conDatabase.Close()

Response.Redirect(

"success.html")EndSub

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>iNSERIMENTO</title><LINKhref="mauro.css"rel=stylesheet>

<

scripttype="text/javascript"language="javascript">

<!--

function

popopen(){

window.open(

"upload/upload.aspx","name"," toolbar=no,directories=no,menubar=no,width=300,height=300,top=100,left=150,resizable=no,scrollbars=yes");

}

// -->

</

script>

</

head>

<

bodybgcolor="#DFE5F2"style="font-size: 12pt"><formid="form1"runat="server"><div> <asp:LabelID="Label1"runat="server"BackColor="#8080FF"BorderColor="Black"ForeColor="Black"Height="29px"Text="FORM INSERIMENTO DOCUMENTO"Width="371px"Font-Bold="True"Font-Names="Verdana"Font-Size="14pt"Font-Underline="True"></asp:Label><br/><br/><br/><B><spanstyle="font-family: Verdana">NOME </span></B><asp:TextBoxID="txtNome"runat="server"></asp:TextBox> <B><spanstyle="font-family: Verdana">COGNOME </span></B><asp:TextBoxID="txtCognome"runat="server"></asp:TextBox><br/><br/><B><spanstyle="font-family: Verdana">TITOLO </span></B><asp:TextBoxID="txtTitolo"runat="server"></asp:TextBox><br/><br/>

<ahref="javascript:popopen()">CARICA DOCUMENTO</a><br/><br/>

<asp:ButtonID="Button1"runat="server"OnClick="Button_Clic"Text="ADD"Font-Bold="True"Font-Names="Verdana"Font-Size="12pt"Width="160px"/></div><br><br></form>

</

body>

</

html>Remove the "=" sign in the INSERT statement.|||

ndinakar:

Remove the "=" sign in the INSERT statement.

damn!ehehehe ; ) thanks you....