finding columns

In a specific database. I would like to check to see if a particular column
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and Query Analyzer for example. You can do it programatically via ADO.OpenSchema(), sp_help, or DMO... Of course querying the system tables is always an option -- something like (this doesn't narrow to the database level)
select so.name as 'Table', sc.Name as 'Column
from syscolumns s
join sysobjects so on so.id = sc.i
wher
xo.xtype = 'u
--an
--sc.name like '%column_name_to_find%
group by so.name,sc.nam
...the usual caveats apply (MS does not recommend using the system tables, etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > In a specific database. I would like to check to see if a particular
> column
> > exists in all my tables i.e user created tables. I know I have to use
the
> > sysobjects and syscolumns table. But I am sorta at loss to find out how
> they
> > are related.
> >
> > Please Help
> >
> > VJ
> >
> >
>

finding columns

In a specific database. I would like to check to see if a particular column
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and
Query Analyzer for example. You can do it programatically via ADO.OpenSche
ma(), sp_help, or DMO... Of course querying the system tables is always an
option -- something like (t
his doesn't narrow to the database level):
select so.name as 'Table', sc.Name as 'Column'
from syscolumns sc
join sysobjects so on so.id = sc.id
where
xo.xtype = 'u'
--and
--sc.name like '%column_name_to_find%'
group by so.name,sc.name
...the usual caveats apply (MS does not recommend using the system tables,
etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> column
the
> they
>

Finding and Deleting Table

How we can find out by Query that wether a particular table exists?

Quote:

Originally Posted by sajithamol

How we can find out by Query that wether a particular table exists?

Hi,
you can find the table in sys.tables
if exists(select name from sys.tables where name='table name')
drop table tablename|||you can find tables by using this query

Select name from sysobjects where xtype = 'u' and name like 'tblname%'

and for deleting the table

drop table tblName

Finding a column in all tables within the DB

How do I list all tables that contain a particular column? For example, how do I list all tables that contain the column lastname within my database called manager? Thanx.

Quote:

Originally Posted by Rob0000

How do I list all tables that contain a particular column? For example, how do I list all tables that contain the column lastname within my database called manager? Thanx.

i ran into this issue the other day

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%lastname %' )

if your doing this in SQL server just open up a sql command and execute this

find who is accessing a database

Is there an easy way to find out who is currently accessing a particular
database? We use SQL authentication.
Thanks.How about sp_who or sp_who2?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Kelley" <tkelley@.company.com> wrote in message news:OEQKYtIwGHA.4160@.TK2MSFTNGP06.phx.gbl...
> Is there an easy way to find out who is currently accessing a particular
> database? We use SQL authentication.
> Thanks.
>|||Do these stored procedures return only active users?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3u%23fuIwGHA.4880@.TK2MSFTNGP04.phx.gbl...
> How about sp_who or sp_who2?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:OEQKYtIwGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> Is there an easy way to find out who is currently accessing a particular
>> database? We use SQL authentication.
>> Thanks.|||What do you mean by "active"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Kelley" <tkelley@.company.com> wrote in message news:%235u8reJwGHA.1436@.TK2MSFTNGP02.phx.gbl...
> Do these stored procedures return only active users?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u3u%23fuIwGHA.4880@.TK2MSFTNGP04.phx.gbl...
>> How about sp_who or sp_who2?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Tim Kelley" <tkelley@.company.com> wrote in message
>> news:OEQKYtIwGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> Is there an easy way to find out who is currently accessing a particular
>> database? We use SQL authentication.
>> Thanks.
>|||Jus to add, you can also use sp_lock or the following query:
select p.*
from syslockinfo l, sysprocesses p
where l.req_spid = p.spid
and l.rsc_type = 2
and l.rsc_dbid = db_id('yourDB')
This query or sp_lock will capture anyone that accesses a particular
database, including the one whose current database is not the one you are
investigating. For instance, you can run a query accessing pubs from master,
sp_who will show master, but not pubs. sp_lock or the above query will show
that you are putting a DB lock on pubs, thus you are accessing pubs.
Linchi
"Tim Kelley" wrote:
> Do these stored procedures return only active users?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u3u%23fuIwGHA.4880@.TK2MSFTNGP04.phx.gbl...
> > How about sp_who or sp_who2?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Tim Kelley" <tkelley@.company.com> wrote in message
> > news:OEQKYtIwGHA.4160@.TK2MSFTNGP06.phx.gbl...
> >> Is there an easy way to find out who is currently accessing a particular
> >> database? We use SQL authentication.
> >>
> >> Thanks.
>
>|||Tim Kelley wrote:
> Is there an easy way to find out who is currently accessing a particular
> database? We use SQL authentication.
> Thanks.
>
Define "currently". If you want to know, RIGHT NOW, at this VERY
MOMENT, who is using a database, then you can use sp_who, sp_who2, or
just query the master..sysprocesses table. Any of these will show you
the current connections to the database.
If you want to know, over a specific period of time, who used a
database, then you'll have to use Profiler to capture the activity in
the database.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

Find Table Size

Env: SQL Server 2000

The following sql stmt seems to find a particular table's size
programmatically:

select top 1 [rows],rowcnt
from sysindexes
where ID = object_id('aUserTable')
and status = 0
and used > 0

However,
a) I'm not 100% sure of its consistency;
b) Both [rows] col and [rowcnt] col seems to produce same data, which
one is supposed to be more accurate (or more up to date)?

TIA.NickName (dadada@.rock.com) writes:
> The following sql stmt seems to find a particular table's size
> programmatically:
> select top 1 [rows],rowcnt
> from sysindexes
> where ID = object_id('aUserTable')
> and status = 0
> and used > 0
> However,
> a) I'm not 100% sure of its consistency;
> b) Both [rows] col and [rowcnt] col seems to produce same data, which
> one is supposed to be more accurate (or more up to date)?

A TOP 1 without ORDER BY is not a good thing. However, if you with
"size" means rowcount, this may do. Better though is to add the
"AND indid IN (0, 1)" to the WHERE clause. (There is never rows for
both 0 and 1, but always for exactly one of them.)

The values in sysindexes are not the exact values, for that you need to
do SELECT COUNT(*). However, a DBCC UPDATEUSAGE before you run the
SELECT query will give you good accuracy.

Judging from the documentation, rowcnt is the better column to use.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Yes, when I say table size I meant row count of a table.

Results of some testing of the following queries:

select rowcnt
from sysindexes
where ID = object_id('customer')
-- and status = 0
and used > 0
AND indid IN (0, 1)
produces correct resultset

select rowcnt
from sysindexes
where ID = object_id('customer')
and status = 0
and used > 0
AND indid IN (0, 1)

produces incorrect resultset (zero count)

Not to use SELECT COUNT(*) ... is because I intend to get row count for
each and all tables of a huge database, COUNT would take considerable
longer to do.

You're the man!

Don

Find size of row in table

Is there any way to find the size of a particular row in a table?
Thanks,
TomTomT wrote:
> Is there any way to find the size of a particular row in a table?
> Thanks,
> Tom
For storage usage, you can use datalength(). For number of characters,
you can use LEN().
create table test1 (
id int identity,
col1 int,
col2 varchar(100),
col3 nvarchar(50),
col4 nchar(10),
col5 datetime)
insert into test1 values (5, 'Hi there', 'hi there unicode', '',
getdate())
Select * from test1
Select datalength(col1) +
datalength(col2) +
datalength(col3) +
datalength(col4) +
datalength(col5)
From test1
Where ID = 1
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for your reply. I tried your suggestion, and after adjusting for
possible null values in some of the columns, got the results I needed.
The reason I'm doing this is due to a strange problem with a row in a table.
This table has a variety of columns, one of which is for notes, and it is
varchar(3000).
On the row in question, I am trying to insert notes which are 1900
characters (including spaces, line breakes, etc.).
The problem is, for some reason I cannot insert more than 1023 characters
into the column. I'm testing this by going directly into the row in the
table, and typing in the characters. When I try to go beyond 1023 characters,
all that happens is my system beeps, and no characters are accepted. Then
entire size of the row, via your suggestion, is 1239.
Curiously, the row just before this has a total length of 2481, and the
number of characters in the notes column is 2258.
I'm stumped as to why I can't get more text into the column, I even removed
some characters from other columns in the row, just to see if this would make
a difference, but it did not.
Do you have any idea what might be happening here?
Thanks for your help,
Tom
"David Gugick" wrote:
> TomT wrote:
> > Is there any way to find the size of a particular row in a table?
> >
> > Thanks,
> >
> > Tom
> For storage usage, you can use datalength(). For number of characters,
> you can use LEN().
> create table test1 (
> id int identity,
> col1 int,
> col2 varchar(100),
> col3 nvarchar(50),
> col4 nchar(10),
> col5 datetime)
> insert into test1 values (5, 'Hi there', 'hi there unicode', '',
> getdate())
> Select * from test1
> Select datalength(col1) +
> datalength(col2) +
> datalength(col3) +
> datalength(col4) +
> datalength(col5)
> From test1
> Where ID = 1
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||TomT wrote:
> David,
> Thanks for your reply. I tried your suggestion, and after adjusting
> for possible null values in some of the columns, got the results I
> needed.
> The reason I'm doing this is due to a strange problem with a row in a
> table. This table has a variety of columns, one of which is for
> notes, and it is varchar(3000).
> On the row in question, I am trying to insert notes which are 1900
> characters (including spaces, line breakes, etc.).
> The problem is, for some reason I cannot insert more than 1023
> characters into the column. I'm testing this by going directly into
> the row in the table, and typing in the characters. When I try to go
> beyond 1023 characters, all that happens is my system beeps, and no
> characters are accepted. Then entire size of the row, via your
> suggestion, is 1239.
> Curiously, the row just before this has a total length of 2481, and
> the number of characters in the notes column is 2258.
> I'm stumped as to why I can't get more text into the column, I even
> removed some characters from other columns in the row, just to see if
> this would make a difference, but it did not.
> Do you have any idea what might be happening here?
> Thanks for your help,
> Tom
Unless this is a small table, you should probably get that varchar(3000)
out of there or change it to a text/ntext. If that column is even 1/3
full, you'll end up with very large row sizes and decrease your row
density (which slows everything down). 8060 is the limit on row length,
but it doesn't appear you are near that.
You say, "by going directly into the row in the table". That term means
nothing in the relational database world. There is no way to go directly
into a row and edit, short of opening a data file in a editor and
directly manipulating the file. You are using some application and that
application likely has column entry limits of 1K or 1024 characters. Try
entering the data using SQL or another application.
--
David Gugick
Imceda Software
www.imceda.com

Find size of row in table

Is there any way to find the size of a particular row in a table?
Thanks,
Tom
TomT wrote:
> Is there any way to find the size of a particular row in a table?
> Thanks,
> Tom
For storage usage, you can use datalength(). For number of characters,
you can use LEN().
create table test1 (
id int identity,
col1 int,
col2 varchar(100),
col3 nvarchar(50),
col4 nchar(10),
col5 datetime)
insert into test1 values (5, 'Hi there', 'hi there unicode', '',
getdate())
Select * from test1
Select datalength(col1) +
datalength(col2) +
datalength(col3) +
datalength(col4) +
datalength(col5)
From test1
Where ID = 1
David Gugick
Imceda Software
www.imceda.com
|||David,
Thanks for your reply. I tried your suggestion, and after adjusting for
possible null values in some of the columns, got the results I needed.
The reason I'm doing this is due to a strange problem with a row in a table.
This table has a variety of columns, one of which is for notes, and it is
varchar(3000).
On the row in question, I am trying to insert notes which are 1900
characters (including spaces, line breakes, etc.).
The problem is, for some reason I cannot insert more than 1023 characters
into the column. I'm testing this by going directly into the row in the
table, and typing in the characters. When I try to go beyond 1023 characters,
all that happens is my system beeps, and no characters are accepted. Then
entire size of the row, via your suggestion, is 1239.
Curiously, the row just before this has a total length of 2481, and the
number of characters in the notes column is 2258.
I'm stumped as to why I can't get more text into the column, I even removed
some characters from other columns in the row, just to see if this would make
a difference, but it did not.
Do you have any idea what might be happening here?
Thanks for your help,
Tom
"David Gugick" wrote:

> TomT wrote:
> For storage usage, you can use datalength(). For number of characters,
> you can use LEN().
> create table test1 (
> id int identity,
> col1 int,
> col2 varchar(100),
> col3 nvarchar(50),
> col4 nchar(10),
> col5 datetime)
> insert into test1 values (5, 'Hi there', 'hi there unicode', '',
> getdate())
> Select * from test1
> Select datalength(col1) +
> datalength(col2) +
> datalength(col3) +
> datalength(col4) +
> datalength(col5)
> From test1
> Where ID = 1
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||TomT wrote:
> David,
> Thanks for your reply. I tried your suggestion, and after adjusting
> for possible null values in some of the columns, got the results I
> needed.
> The reason I'm doing this is due to a strange problem with a row in a
> table. This table has a variety of columns, one of which is for
> notes, and it is varchar(3000).
> On the row in question, I am trying to insert notes which are 1900
> characters (including spaces, line breakes, etc.).
> The problem is, for some reason I cannot insert more than 1023
> characters into the column. I'm testing this by going directly into
> the row in the table, and typing in the characters. When I try to go
> beyond 1023 characters, all that happens is my system beeps, and no
> characters are accepted. Then entire size of the row, via your
> suggestion, is 1239.
> Curiously, the row just before this has a total length of 2481, and
> the number of characters in the notes column is 2258.
> I'm stumped as to why I can't get more text into the column, I even
> removed some characters from other columns in the row, just to see if
> this would make a difference, but it did not.
> Do you have any idea what might be happening here?
> Thanks for your help,
> Tom
Unless this is a small table, you should probably get that varchar(3000)
out of there or change it to a text/ntext. If that column is even 1/3
full, you'll end up with very large row sizes and decrease your row
density (which slows everything down). 8060 is the limit on row length,
but it doesn't appear you are near that.
You say, "by going directly into the row in the table". That term means
nothing in the relational database world. There is no way to go directly
into a row and edit, short of opening a data file in a editor and
directly manipulating the file. You are using some application and that
application likely has column entry limits of 1K or 1024 characters. Try
entering the data using SQL or another application.
David Gugick
Imceda Software
www.imceda.com

find query plan

We are using SQL 2005.
What is the query that uses DMVs to track a query plan of a particular sproc
running on a server?
we noticed a particular sproc thats performing poorly when being called from
the client vs when being called from QA and hence wanted to get the query
plan that was called from the client
I know you can get that in 2005 which was not possible in 2000.
What is the query ? I think I have seen it presented and it seems abit
complex that calls plan handles and has a few joins..
ThanksHassan
Try this one ( I could not remember who wrote this function)
CREATE FUNCTION statement_level_query_plan(
@.handle as varbinary(64) -- Handle for the overall query plan
)
RETURNS TABLE as
RETURN (
select
statement_nbr, -- Sequential number of statement
within batch or SP
statement_type, -- SELECT, INSERT, UPDATE, etc
statement_subtree_cost, -- Estimated Query Cost
statement_estimated_rows, -- Estimated Rows Returned
statement_optimization_level, -- FULL or TRIVIAL
statement_text, -- Text of query
statement_plan -- XML Plan To view as a graphical
plan
-- save the column
output to a file with extension .SQLPlan
-- then reopen the
file by double-clicking
from (
select
C.value('@.StatementId','int') as statement_nbr,
C.value('(./@.StatementText)','nvarchar(max)') as
statement_text,
C.value('(./@.StatementType)','varchar(20)') as
statement_type,
C.value('(./@.StatementSubTreeCost)','float') as
statement_subtree_cost,
C.value('(./@.StatementEstRows)','float') as
statement_estimated_rows,
C.value('(./@.StatementOptmLevel)','varchar(20)') as
statement_optimization_level,
-- Construct the XML headers around the single plan that will
permit
-- this column to be used as a graphical showplan.
-- Only generate plan columns where statement has an associated
plan
C.query('declare namespace
PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
if (./PLN:QueryPlan or ./PLN:Condition/PLN:QueryPlan)
then
<PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
{ ./attribute::* }
{ ./descendant::PLN:QueryPlan[1] }
</PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
else ()
') as statement_plan
from
sys.dm_exec_query_plan(@.handle)
CROSS APPLY
-- This expression finds all nodes containing attribute
StatementText
-- regardless of how deep they are in the potentially nested
batch hierarchy
-- The results of this expression are processed by the Select
expressions above
query_plan.nodes('declare namespace
PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
as T(C)
) x
)
--So, the query to report all executing
--SQL statements and their plans is:
select pln.*, req.* from sys.dm_exec_requests as req
CROSS APPLY statement_level_query_plan(plan_handle) as pln
where statement_text like
'%' +
replace(
left(
substring((select text from
master.sys.dm_exec_sql_text(sql_handle)),
statement_start_offset/2,
1+ case when statement_end_offset = -1
then LEN((select text from
master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
else statement_end_offset/2 -
statement_start_offset/2
end)
,3000)
, '[','[[]') + '%'
--Even more useful,
--the function can be joined to the
--view sys.dm_exec_query_stats that reports
-- per-statement IO, CPU and duration statistics.
-- For example, to find the plans for the
--10 queries with longest average durations:
select pln.*, req.* from
(select top 10 * from sys.dm_exec_query_stats
order by total_elapsed_time/execution_count desc ) req
CROSS APPLY statement_level_query_plan(plan_handle) as pln
where statement_text like
'%' +
replace(
left(
substring((select text from
master.sys.dm_exec_sql_text(sql_handle)),
statement_start_offset/2,
1+ case when statement_end_offset = -1
then LEN((select text from
master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
else statement_end_offset/2 -
statement_start_offset/2
end)
,3000)
, '[','[[]') + '%'
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
> We are using SQL 2005.
> What is the query that uses DMVs to track a query plan of a particular
> sproc running on a server?
> we noticed a particular sproc thats performing poorly when being called
> from the client vs when being called from QA and hence wanted to get the
> query plan that was called from the client
> I know you can get that in 2005 which was not possible in 2000.
> What is the query ? I think I have seen it presented and it seems abit
> complex that calls plan handles and has a few joins..
> Thanks
>|||Uri,
Could you attach the file ? The entire formatting is not coming along well
and hence nor parsing.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e5uuDDUFIHA.1056@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Try this one ( I could not remember who wrote this function)
> CREATE FUNCTION statement_level_query_plan(
> @.handle as varbinary(64) -- Handle for the overall query plan
> )
> RETURNS TABLE as
> RETURN (
> select
> statement_nbr, -- Sequential number of statement
> within batch or SP
> statement_type, -- SELECT, INSERT, UPDATE,
> etc
> statement_subtree_cost, -- Estimated Query Cost
> statement_estimated_rows, -- Estimated Rows Returned
> statement_optimization_level, -- FULL or TRIVIAL
> statement_text, -- Text of query
> statement_plan -- XML Plan To view as a graphical
> plan
> -- save the
> column output to a file with extension .SQLPlan
> -- then reopen
> the file by double-clicking
> from (
> select
> C.value('@.StatementId','int') as statement_nbr,
> C.value('(./@.StatementText)','nvarchar(max)') as
> statement_text,
> C.value('(./@.StatementType)','varchar(20)') as
> statement_type,
> C.value('(./@.StatementSubTreeCost)','float') as
> statement_subtree_cost,
> C.value('(./@.StatementEstRows)','float') as
> statement_estimated_rows,
> C.value('(./@.StatementOptmLevel)','varchar(20)') as
> statement_optimization_level,
> -- Construct the XML headers around the single plan that will
> permit
> -- this column to be used as a graphical showplan.
> -- Only generate plan columns where statement has an
> associated plan
> C.query('declare namespace
> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> if (./PLN:QueryPlan or
> ./PLN:Condition/PLN:QueryPlan)
> then
>
> <PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
> { ./attribute::* }
> { ./descendant::PLN:QueryPlan[1] }
>
> </PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
> else ()
> ') as statement_plan
> from
> sys.dm_exec_query_plan(@.handle)
> CROSS APPLY
> -- This expression finds all nodes containing attribute
> StatementText
> -- regardless of how deep they are in the potentially nested
> batch hierarchy
> -- The results of this expression are processed by the Select
> expressions above
> query_plan.nodes('declare namespace
> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>
> /PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
> as T(C)
> ) x
> )
>
> --So, the query to report all executing
> --SQL statements and their plans is:
> select pln.*, req.* from sys.dm_exec_requests as req
> CROSS APPLY statement_level_query_plan(plan_handle) as pln
> where statement_text like
> '%' +
> replace(
> left(
> substring((select text from
> master.sys.dm_exec_sql_text(sql_handle)),
> statement_start_offset/2,
> 1+ case when statement_end_offset = -1
> then LEN((select text from
> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
> else statement_end_offset/2 -
> statement_start_offset/2
> end)
> ,3000)
> , '[','[[]') + '%'
>
> --Even more useful,
> --the function can be joined to the
> --view sys.dm_exec_query_stats that reports
> -- per-statement IO, CPU and duration statistics.
> -- For example, to find the plans for the
> --10 queries with longest average durations:
>
> select pln.*, req.* from
> (select top 10 * from sys.dm_exec_query_stats
> order by total_elapsed_time/execution_count desc ) req
> CROSS APPLY statement_level_query_plan(plan_handle) as pln
> where statement_text like
> '%' +
> replace(
> left(
> substring((select text from
> master.sys.dm_exec_sql_text(sql_handle)),
> statement_start_offset/2,
> 1+ case when statement_end_offset = -1
> then LEN((select text from
> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
> else statement_end_offset/2 -
> statement_start_offset/2
> end)
> ,3000)
> , '[','[[]') + '%'
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> We are using SQL 2005.
>> What is the query that uses DMVs to track a query plan of a particular
>> sproc running on a server?
>> we noticed a particular sproc thats performing poorly when being called
>> from the client vs when being called from QA and hence wanted to get the
>> query plan that was called from the client
>> I know you can get that in 2005 which was not possible in 2000.
>> What is the query ? I think I have seen it presented and it seems abit
>> complex that calls plan handles and has a few joins..
>> Thanks
>|||Sure
"Hassan" <hassan@.hotmail.com> wrote in message
news:%239yIidZFIHA.2004@.TK2MSFTNGP06.phx.gbl...
> Uri,
> Could you attach the file ? The entire formatting is not coming along well
> and hence nor parsing.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e5uuDDUFIHA.1056@.TK2MSFTNGP03.phx.gbl...
>> Hassan
>> Try this one ( I could not remember who wrote this function)
>> CREATE FUNCTION statement_level_query_plan(
>> @.handle as varbinary(64) -- Handle for the overall query plan
>> )
>> RETURNS TABLE as
>> RETURN (
>> select
>> statement_nbr, -- Sequential number of statement
>> within batch or SP
>> statement_type, -- SELECT, INSERT, UPDATE,
>> etc
>> statement_subtree_cost, -- Estimated Query Cost
>> statement_estimated_rows, -- Estimated Rows Returned
>> statement_optimization_level, -- FULL or TRIVIAL
>> statement_text, -- Text of query
>> statement_plan -- XML Plan To view as a
>> graphical
>> plan
>> -- save the
>> column output to a file with extension .SQLPlan
>> -- then reopen
>> the file by double-clicking
>> from (
>> select
>> C.value('@.StatementId','int') as statement_nbr,
>> C.value('(./@.StatementText)','nvarchar(max)') as
>> statement_text,
>> C.value('(./@.StatementType)','varchar(20)') as
>> statement_type,
>> C.value('(./@.StatementSubTreeCost)','float') as
>> statement_subtree_cost,
>> C.value('(./@.StatementEstRows)','float') as
>> statement_estimated_rows,
>> C.value('(./@.StatementOptmLevel)','varchar(20)') as
>> statement_optimization_level,
>> -- Construct the XML headers around the single plan that will
>> permit
>> -- this column to be used as a graphical showplan.
>> -- Only generate plan columns where statement has an
>> associated plan
>> C.query('declare namespace
>> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>> if (./PLN:QueryPlan or
>> ./PLN:Condition/PLN:QueryPlan)
>> then
>>
>> <PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
>> { ./attribute::* }
>> { ./descendant::PLN:QueryPlan[1] }
>>
>> </PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
>> else ()
>> ') as statement_plan
>> from
>> sys.dm_exec_query_plan(@.handle)
>> CROSS APPLY
>> -- This expression finds all nodes containing attribute
>> StatementText
>> -- regardless of how deep they are in the potentially nested
>> batch hierarchy
>> -- The results of this expression are processed by the Select
>> expressions above
>> query_plan.nodes('declare namespace
>> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>>
>> /PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
>> as T(C)
>> ) x
>> )
>>
>> --So, the query to report all executing
>> --SQL statements and their plans is:
>> select pln.*, req.* from sys.dm_exec_requests as req
>> CROSS APPLY statement_level_query_plan(plan_handle) as pln
>> where statement_text like
>> '%' +
>> replace(
>> left(
>> substring((select text from
>> master.sys.dm_exec_sql_text(sql_handle)),
>> statement_start_offset/2,
>> 1+ case when statement_end_offset = -1
>> then LEN((select text from
>> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
>> else statement_end_offset/2 -
>> statement_start_offset/2
>> end)
>> ,3000)
>> , '[','[[]') + '%'
>>
>> --Even more useful,
>> --the function can be joined to the
>> --view sys.dm_exec_query_stats that reports
>> -- per-statement IO, CPU and duration statistics.
>> -- For example, to find the plans for the
>> --10 queries with longest average durations:
>>
>> select pln.*, req.* from
>> (select top 10 * from sys.dm_exec_query_stats
>> order by total_elapsed_time/execution_count desc ) req
>> CROSS APPLY statement_level_query_plan(plan_handle) as pln
>> where statement_text like
>> '%' +
>> replace(
>> left(
>> substring((select text from
>> master.sys.dm_exec_sql_text(sql_handle)),
>> statement_start_offset/2,
>> 1+ case when statement_end_offset = -1
>> then LEN((select text from
>> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
>> else statement_end_offset/2 -
>> statement_start_offset/2
>> end)
>> ,3000)
>> , '[','[[]') + '%'
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> We are using SQL 2005.
>> What is the query that uses DMVs to track a query plan of a particular
>> sproc running on a server?
>> we noticed a particular sproc thats performing poorly when being called
>> from the client vs when being called from QA and hence wanted to get the
>> query plan that was called from the client
>> I know you can get that in 2005 which was not possible in 2000.
>> What is the query ? I think I have seen it presented and it seems abit
>> complex that calls plan handles and has a few joins..
>> Thanks
>>
>
begin 666 Query(Problems).txt
M0U)%051%($953D-424].('-T871E;65N=%]L979E;%]Q=65R>5]P;&%N* T*
M#0H@.(" @.(" @.($!H86YD;&4@.87,@.=F%R8FEN87)Y*#8T*2 M+2!(86YD;&4@.
M9F]R('1H92!O=F5R86QL('%U97)Y('!L86X-"@.T**0T*#0I215154DY3(%1!
M0DQ%(&%S( T*#0I215154DX@.* T*#0H@.('-E;&5C=" -"@.T*(" @.(" @.("!S
M=&%T96UE;G1?;F)R+" @.(" @.(" @.(" @.(" @.(" @.+2T@.4V5Q=65N=&EA;"!N
M=6UB97(@.;V8@.<W1A=&5M96YT('=I=&AI;B!B871C:"!O<B!34 T*#0H@.(" @.
M(" @.('-T871E;65N=%]T>7!E+" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.+2T@.
M4T5,14-4+"!)3E-%4E0L(%501$%412P@.971C#0H-"B @.(" @.(" @.<W1A=&5M
M96YT7W-U8G1R965?8V]S="P@.(" @.(" @.(" @.(" @.(" M+2!%<W1I;6%T960@.
M475E<GD@.0V]S= T*#0H@.(" @.(" @.('-T871E;65N=%]E<W1I;6%T961?<F]W
M<RP@.(" @.(" @.(" @.(" @.+2T@.17-T:6UA=&5D(%)O=W,@.4F5T=7)N960-"@.T*
M(" @.(" @.("!S=&%T96UE;G1?;W!T:6UI>F%T:6]N7VQE=F5L+" @.(" @.(" @.
M("TM($953$P@.;W(@.5%))5DE!3 T*#0H@.(" @.(" @.('-T871E;65N=%]T97AT
M+" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.+2T@.5&5X="!O9B!Q=65R>0T*#0H@.
M(" @.(" @.('-T871E;65N=%]P;&%N(" @.(" @.(" @.(" @.(" @.("TM(%A-3"!0
M;&%N(" @.(%1O('9I97<@.87,@.82!G<F%P:&EC86P@.<&QA;@.T*#0H@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M("TM(" @.(" @.<V%V92!T:&4@.8V]L=6UN(&]U='!U="!T;R!A(&9I;&4@.=VET
M:"!E>'1E;G-I;VX@.+E-13%!L86X-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" M+2 @.(" @.('1H96X@.
M<F5O<&5N('1H92!F:6QE(&)Y(&1O=6)L92UC;&EC:VEN9PT*#0H@.("!F<F]M
M("@.-"@.T*(" @.(" @.("!S96QE8W0@.#0H-"B @.(" @.(" @.(" @.(" @.($,N=F%L
M=64H)T!3=&%T96UE;G1)9"<L)VEN="<I(&%S('-T871E;65N=%]N8G(L#0H-
M"B @.(" @.(" @.(" @.(" @.($,N=F%L=64H)R@.N+T!3=&%T96UE;G1497AT*2<L
M)VYV87)C:&%R*&UA>"DG*2!A<R!S=&%T96UE;G1?=&5X="P-"@.T*(" @.(" @.
M(" @.(" @.(" @.0RYV86QU92@.G*"XO0%-T871E;65N=%1Y<&4I)RPG=F%R8VAA
M<B@.R,"DG*2!A<R!S=&%T96UE;G1?='EP92P-"@.T*(" @.(" @.(" @.(" @.(" @.
M0RYV86QU92@.G*"XO0%-T871E;65N=%-U8E1R965#;W-T*2<L)V9L;V%T)RD@.
M87,@.<W1A=&5M96YT7W-U8G1R965?8V]S="P-"@.T*(" @.(" @.(" @.(" @.(" @.
M0RYV86QU92@.G*"XO0%-T871E;65N=$5S=%)O=W,I)RPG9FQO870G*2!A<R!S
M=&%T96UE;G1?97-T:6UA=&5D7W)O=W,L#0H-"B @.(" @.(" @.(" @.(" @.($,N
M=F%L=64H)R@.N+T!3=&%T96UE;G1/<'1M3&5V96PI)RPG=F%R8VAA<B@.R,"DG
M*2!A<R!S=&%T96UE;G1?;W!T:6UI>F%T:6]N7VQE=F5L+ T*#0HM+2 @.(" @.
M(" @.(" @.("!#;VYS=')U8W0@.=&AE(%A-3"!H96%D97)S(&%R;W5N9"!T:&4@.
M<VEN9VQE('!L86X@.=&AA="!W:6QL('!E<FUI= T*#0HM+2 @.(" @.(" @.(" @.
M("!T:&ES(&-O;'5M;B!T;R!B92!U<V5D(&%S(&$@.9W)A<&AI8V%L('-H;W=P
M;&%N+@.T*#0HM+2 @.(" @.(" @.(" @.("!/;FQY(&=E;F5R871E('!L86X@.8V]L
M=6UN<R!W:&5R92!S=&%T96UE;G0@.:&%S(&%N(&%S<V]C:6%T960@.<&QA;@.T*
M#0H@.(" @.(" @.(" @.(" @.("!#+G%U97)Y*"=D96-L87)E(&YA;65S<&%C92!0
M3$X](FAT=' Z+R]S8VAE;6%S+FUI8W)O<V]F="YC;VTO<W%L<V5R=F5R+S(P
M,#0O,#<O<VAO=W!L86XB.PT*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(&EF
M("@.N+U!,3CI1=65R>5!L86X@.;W(@.+B]03$XZ0V]N9&ET:6]N+U!,3CI1=65R
M>5!L86XI( T*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.('1H96X-"@.T*(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q03$XZ4VAO=U!L86Y834P^
M/%!,3CI"871C:%-E<75E;F-E/CQ03$XZ0F%T8V@.^/%!,3CI3=&%T96UE;G1S
M/CQ03$XZ4W1M=%-I;7!L93X-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.>R N+V%T=')I8G5T93HZ*B!]#0H-"B @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.('L@.+B]D97-C96YD86YT.CI03$XZ475E<GE0;&%N6S%=M('T-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#PO4$Q..E-T
M;713:6UP;&4^/"]03$XZ4W1A=&5M96YT<SX\+U!,3CI"871C:#X\+U!,3CI"
M871C:%-E<75E;F-E/CPO4$Q..E-H;W=0;&%N6$U,/@.T*#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(&5L<V4@.*"D-"@.T*(" @.(" @.(" @.(" @.(" @.)RD@.87,@.
M<W1A=&5M96YT7W!L86X-"@.T*(" @.(" @.("!F<F]M( T*#0H@.(" @.(" @.(" @.
M(" @.("!S>7,N9&U?97AE8U]Q=65R>5]P;&%N*$!H86YD;&4I#0H-"B @.(" @.
M(" @.0U)/4U,@.05!03%D@.#0H-"BTM(" @.(" @.(" @.(" @.(%1H:7,@.97AP<F5S
M<VEO;B!F:6YD<R!A;&P@.;F]D97,@.8V]N=&%I;FEN9R!A='1R:6)U=&4@.4W1A
M=&5M96YT5&5X= T*#0HM+2 @.(" @.(" @.(" @.("!R96=A<F1L97-S(&]F(&AO
M=R!D965P('1H97D@.87)E(&EN('1H92!P;W1E;G1I86QL>2!N97-T960@.8F%T
M8V@.@.:&EE<F%R8VAY#0H-"BTM(" @.(" @.(" @.(" @.(%1H92!R97-U;'1S(&]F
M('1H:7,@.97AP<F5S<VEO;B!A<F4@.<')O8V5S<V5D(&)Y('1H92!396QE8W0@.
M97AP<F5S<VEO;G,@.86)O=F4-"@.T*(" @.(" @.(" @.(" @.(" @.<75E<GE?<&QA
M;BYN;V1E<R@.G9&5C;&%R92!N86UE<W!A8V4@.4$Q./2)H='1P.B\O<V-H96UA
M<RYM:6-R;W-O9G0N8V]M+W-Q;'-E<G9E<B\R,# T+S W+W-H;W=P;&%N(CL-
M"@.T*(" @.(" @.(" @.(" @.(" @.("]03$XZ4VAO=U!L86Y834PO4$Q..D)A=&-H
M4V5Q=65N8V4O4$Q..D)A=&-H+U!,3CI3=&%T96UE;G1S+V1E<V-E;F1A;G0Z
M.BI;871T<FEB=71E.CI3=&%T96UE;G1497AT72<I( T*#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(&%S(%0H0RD@.#0H-"B @.(" @.(" @.*2!X#0H-"B @.*0T*
M#0H-"B M+5-O+"!T:&4@.<75E<GD@.=&\@.<F5P;W)T(&%L;"!E>&5C=71I;F<@.
M#0HM+5-13"!S=&%T96UE;G1S(&%N9"!T:&5I<B!P;&%N<R!I<SH@.#0H-"G-E
M;&5C="!P;&XN*BP@.<F5Q+BH@.9G)O;2!S>7,N9&U?97AE8U]R97%U97-T<R!A
M<R!R97$-"@.T*0U)/4U,@.05!03%D@.<W1A=&5M96YT7VQE=F5L7W%U97)Y7W!L
M86XH<&QA;E]H86YD;&4I(&%S('!L;@.T*#0IW:&5R92!S=&%T96UE;G1?=&5X
M="!L:6ME#0H-"B<E)R K#0H-"G)E<&QA8V4H#0H-"FQE9G0H#0H-"B @.(" @.
M(" @.(" @.(" @.('-U8G-T<FEN9R@.H<V5L96-T('1E>'0@.9G)O;2!M87-T97(N
M<WES+F1M7V5X96-?<W%L7W1E>'0H<W%L7VAA;F1L92DI+" -"@.T*(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.("!S=&%T96UE;G1?<W1A<G1?;V9F<V5T+S(L( T*
M#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#$K(" @.(" @.8V%S92!W:&5N('-T
M871E;65N=%]E;F1?;V9F<V5T(#T@.+3$@.#0H-"B @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.('1H96X@.3$5.*"AS96QE8W0@.=&5X="!F<F]M(&UA<W1E
M<BYS>7,N9&U?97AE8U]S<6Q?=&5X="AS<6Q?:&%N9&QE*2DI("T@.<W1A=&5M
M96YT7W-T87)T7V]F9G-E="\R#0H-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.("!E;'-E('-T871E;65N=%]E;F1?;V9F<V5T+S(@.+2!S=&%T96UE
M;G1?<W1A<G1?;V9F<V5T+S(@.#0H-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(&5N9"D@.#0H-"B @.(" @.(" @.+#,P,# I#0H-"BP@.)ULG+"=;6UTG
M*2 K("<E)PT*#0H-"BTM179E;B!M;W)E('5S969U;"P@.#0HM+71H92!F=6YC
M=&EO;B!C86X@.8F4@.:F]I;F5D('1O('1H92 -"BTM=FEE=R!S>7,N9&U?97AE
M8U]Q=65R>5]S=&%T<R!T:&%T(')E<&]R=',-"BTM('!E<BUS=&%T96UE;G0@.
M24\L($-052!A;F0@.9'5R871I;VX@.<W1A=&ES=&EC<RX@.( T*+2T@.1F]R(&5X
M86UP;&4L('1O(&9I;F0@.=&AE('!L86YS(&9O<B!T:&4@.#0HM+3$P('%U97)I
M97,@.=VET:"!L;VYG97-T(&%V97)A9V4@.9'5R871I;VYS.@.T*#0H@.#0H-"G-E
M;&5C="!P;&XN*BP@.<F5Q+BH@.9G)O;0T*#0HH<V5L96-T('1O<" Q," J(&9R
M;VT@.<WES+F1M7V5X96-?<75E<GE?<W1A=',@.#0H-"F]R9&5R(&)Y('1O=&%L
M7V5L87!S961?=&EM92]E>&5C=71I;VY?8V]U;G0@.9&5S8R I(')E<0T*#0I#
M4D]34R!!4%!,62!S=&%T96UE;G1?;&5V96Q?<75E<GE?<&QA;BAP;&%N7VAA
M;F1L92D@.87,@.<&QN#0H-"G=H97)E('-T871E;65N=%]T97AT(&QI:V4-"@.T*
M)R4G("L-"@.T*<F5P;&%C92@.-"@.T*;&5F="@.-"@.T*(" @.(" @.(" @.(" @.(" @.
M<W5B<W1R:6YG*"AS96QE8W0@.=&5X="!F<F]M(&UA<W1E<BYS>7,N9&U?97AE
M8U]S<6Q?=&5X="AS<6Q?:&%N9&QE*2DL( T*#0H@.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.('-T871E;65N=%]S=&%R=%]O9F9S970O,BP@.#0H-"B @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.,2L@.(" @.("!C87-E('=H96X@.<W1A=&5M96YT7V5N
M9%]O9F9S970@./2 M,2 -"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.=&AE;B!,14XH*'-E;&5C="!T97AT(&9R;VT@.;6%S=&5R+G-Y<RYD;5]E
M>&5C7W-Q;%]T97AT*'-Q;%]H86YD;&4I*2D@.+2!S=&%T96UE;G1?<W1A<G1?
M;V9F<V5T+S(-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.96QS
M92!S=&%T96UE;G1?96YD7V]F9G-E="\R("T@.<W1A=&5M96YT7W-T87)T7V]F
M9G-E="\R( T*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.("!E;F0I
M( T*#0H@.(" @.(" @.("PS,# P*0T*#0HL("=;)RPG6UM=)RD@.*R G)2<-"@.T*
`
end

Find out users in a particular Windows group ?

We have our admins create groups containing Windows users ... Is there a
way to check through SQL which members belong to that group ? Using SQL 2000Hi Hassan,
SQL Server doesn't store which Windows users are in a Windows group. So the
only way to find out is to use the command shell with something like (I
don't know the exact syntax of the NET command):
EXEC xp_cmdshell 'NET GROUP <group name> /DOMAIN'
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e5a3$$rxDHA.1996@.TK2MSFTNGP12.phx.gbl...
> We have our admins create groups containing Windows users ... Is there a
> way to check through SQL which members belong to that group ? Using SQL
2000
>

Find in SPs option for SQL

Hi All,
Just a quick question, is there any option to search in all stored
procedures in a database for a particular string, without scripting out
the whole lot?
Thanks
WillHow about a query against syscomments (2000) or sys.sql_modules (2005), for
example:
USe pubs
SELECT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%authors%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1146735219.374369.41630@.g10g2000cwb.googlegroups.com...
> Hi All,
> Just a quick question, is there any option to search in all stored
> procedures in a database for a particular string, without scripting out
> the whole lot?
> Thanks
> Will
>|||Thanks Tibor,
I was hoping that there was some nice GUI addition to Management
studio, but I suppose it's going to have to be a SQL query
Will

Find Counties in area

We have the need to determine surrounding counties of a particular county.

For example how can I determine, if I entered a zip code for Contra Costa and the property is also right on the line of alameda that Alameda is a neighboring county of Contra Costa

Could it be determined by the longitude and latitude of the zip code in comparison with the counties?

Hello,

Although I'm sure a geospacial co-ordinate system could be used to determine this, most people think it overkill and just use a proximity table :)

tCounty (CountyID int, CountyName varchar)

tCountyAdjoined(CountyID int FK to tCounty.CountyID, AdjoiningCountyID int FK to tCounty.CountyID)

So if Contra Costa was near Alameda, tCountry might contain rows:

1, Contra Costa

2, Alameda

And tCountyAdjoined would contain:

1, 2

Obviously, a simple join then returns all counties that adjoin a particular County.

Unfortunatly, you'll need to manually determine and input the proximity values (unless your postal service supplies these details as in Australia.)

Cheers,

Rob

Find all the objects(stored procedures) that uses a particular view

Hi,
I need to modify a View. But I don't know how many objects(stored procedure)
in SQL Server uses this view.
Is there a where to find out which objects use this particular view.
I can't do this manually because my DB contains 100's of stored procedures
Thanks
Kiran
you can check the dependancies in EM, or in query analyzer use sp_depends
sp_depends
Displays information about database object dependencies (for example, the
views and procedures that depend on a table or view, and the tables and
views that are depended on by the view or procedure). References to objects
outside the current database are not reported.
Syntax
sp_depends [ @.objname = ] 'object'
Arguments
[@.objname =] 'object'
The database object to examine for dependencies. The object can be a table,
view, stored procedure, or trigger. object is nvarchar(776), with no
default.
Return Code Values
0 (success) or 1 (failure)
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to modify a View. But I don't know how many objects(stored
procedure)
> in SQL Server uses this view.
> Is there a where to find out which objects use this particular view.
> I can't do this manually because my DB contains 100's of stored procedures
> Thanks
> Kiran
>
|||Hi Simon,
The "sp_depends view_name" returns the list of column names that are in the
view and the related object name, type etc.....
I want to get the names of object that uses this View.
For eg. I have a view vew_employees. There are 4 stored procedures that uses
this view vew_employees.
I need something that will return me the names of these 4 sp.s
Thanks
Kiran
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> you can check the dependancies in EM, or in query analyzer use sp_depends
> sp_depends
> Displays information about database object dependencies (for example, the
> views and procedures that depend on a table or view, and the tables and
> views that are depended on by the view or procedure). References to
> objects
> outside the current database are not reported.
> Syntax
> sp_depends [ @.objname = ] 'object'
> Arguments
> [@.objname =] 'object'
> The database object to examine for dependencies. The object can be a
> table,
> view, stored procedure, or trigger. object is nvarchar(776), with no
> default.
> Return Code Values
> 0 (success) or 1 (failure)
>
> "Kiran Kumar" <nospam@.spam.com> wrote in message
> news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> procedure)
>
|||Assuming your view name is somewhat unique, this will give you an idea:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%view_name%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses[vbcol=seagreen]
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
sp_depends[vbcol=seagreen]
the
>
|||But it does return those 4 procs - within the name column of the output. Is
this not what you are looking for?
In enterprise manager, right click the view, go to "all tasks", and click
"display dependancies...". This will show you the same thing
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses[vbcol=seagreen]
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
sp_depends[vbcol=seagreen]
the
>

Find all the objects(stored procedures) that uses a particular view

Hi,
I need to modify a View. But I don't know how many objects(stored procedure)
in SQL Server uses this view.
Is there a where to find out which objects use this particular view.
I can't do this manually because my DB contains 100's of stored procedures
Thanks
Kiranyou can check the dependancies in EM, or in query analyzer use sp_depends
sp_depends
Displays information about database object dependencies (for example, the
views and procedures that depend on a table or view, and the tables and
views that are depended on by the view or procedure). References to objects
outside the current database are not reported.
Syntax
sp_depends [ @.objname = ] 'object'
Arguments
[@.objname =] 'object'
The database object to examine for dependencies. The object can be a table,
view, stored procedure, or trigger. object is nvarchar(776), with no
default.
Return Code Values
0 (success) or 1 (failure)
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to modify a View. But I don't know how many objects(stored
procedure)
> in SQL Server uses this view.
> Is there a where to find out which objects use this particular view.
> I can't do this manually because my DB contains 100's of stored procedures
> Thanks
> Kiran
>|||Hi Simon,
The "sp_depends view_name" returns the list of column names that are in the
view and the related object name, type etc.....
I want to get the names of object that uses this View.
For eg. I have a view vew_employees. There are 4 stored procedures that uses
this view vew_employees.
I need something that will return me the names of these 4 sp.s
Thanks
Kiran
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> you can check the dependancies in EM, or in query analyzer use sp_depends
> sp_depends
> Displays information about database object dependencies (for example, the
> views and procedures that depend on a table or view, and the tables and
> views that are depended on by the view or procedure). References to
> objects
> outside the current database are not reported.
> Syntax
> sp_depends [ @.objname = ] 'object'
> Arguments
> [@.objname =] 'object'
> The database object to examine for dependencies. The object can be a
> table,
> view, stored procedure, or trigger. object is nvarchar(776), with no
> default.
> Return Code Values
> 0 (success) or 1 (failure)
>
> "Kiran Kumar" <nospam@.spam.com> wrote in message
> news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I need to modify a View. But I don't know how many objects(stored
> procedure)
>> in SQL Server uses this view.
>> Is there a where to find out which objects use this particular view.
>> I can't do this manually because my DB contains 100's of stored
>> procedures
>> Thanks
>> Kiran
>>
>|||Assuming your view name is somewhat unique, this will give you an idea:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%view_name%'
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> > you can check the dependancies in EM, or in query analyzer use
sp_depends
> >
> > sp_depends
> > Displays information about database object dependencies (for example,
the
> > views and procedures that depend on a table or view, and the tables and
> > views that are depended on by the view or procedure). References to
> > objects
> > outside the current database are not reported.
> >
> > Syntax
> > sp_depends [ @.objname = ] 'object'
> >
> > Arguments
> > [@.objname =] 'object'
> >
> > The database object to examine for dependencies. The object can be a
> > table,
> > view, stored procedure, or trigger. object is nvarchar(776), with no
> > default.
> >
> > Return Code Values
> > 0 (success) or 1 (failure)
> >
> >
> >
> > "Kiran Kumar" <nospam@.spam.com> wrote in message
> > news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> I need to modify a View. But I don't know how many objects(stored
> > procedure)
> >> in SQL Server uses this view.
> >>
> >> Is there a where to find out which objects use this particular view.
> >>
> >> I can't do this manually because my DB contains 100's of stored
> >> procedures
> >>
> >> Thanks
> >> Kiran
> >>
> >>
> >
> >
>|||But it does return those 4 procs - within the name column of the output. Is
this not what you are looking for?
In enterprise manager, right click the view, go to "all tasks", and click
"display dependancies...". This will show you the same thing
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> > you can check the dependancies in EM, or in query analyzer use
sp_depends
> >
> > sp_depends
> > Displays information about database object dependencies (for example,
the
> > views and procedures that depend on a table or view, and the tables and
> > views that are depended on by the view or procedure). References to
> > objects
> > outside the current database are not reported.
> >
> > Syntax
> > sp_depends [ @.objname = ] 'object'
> >
> > Arguments
> > [@.objname =] 'object'
> >
> > The database object to examine for dependencies. The object can be a
> > table,
> > view, stored procedure, or trigger. object is nvarchar(776), with no
> > default.
> >
> > Return Code Values
> > 0 (success) or 1 (failure)
> >
> >
> >
> > "Kiran Kumar" <nospam@.spam.com> wrote in message
> > news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> I need to modify a View. But I don't know how many objects(stored
> > procedure)
> >> in SQL Server uses this view.
> >>
> >> Is there a where to find out which objects use this particular view.
> >>
> >> I can't do this manually because my DB contains 100's of stored
> >> procedures
> >>
> >> Thanks
> >> Kiran
> >>
> >>
> >
> >
>

Find all the objects(stored procedures) that uses a particular view

Hi,
I need to modify a View. But I don't know how many objects(stored procedure)
in SQL Server uses this view.
Is there a where to find out which objects use this particular view.
I can't do this manually because my DB contains 100's of stored procedures
Thanks
Kiranyou can check the dependancies in EM, or in query analyzer use sp_depends
sp_depends
Displays information about database object dependencies (for example, the
views and procedures that depend on a table or view, and the tables and
views that are depended on by the view or procedure). References to objects
outside the current database are not reported.
Syntax
sp_depends [ @.objname = ] 'object'
Arguments
[@.objname =] 'object'
The database object to examine for dependencies. The object can be a table,
view, stored procedure, or trigger. object is nvarchar(776), with no
default.
Return Code Values
0 (success) or 1 (failure)
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to modify a View. But I don't know how many objects(stored
procedure)
> in SQL Server uses this view.
> Is there a where to find out which objects use this particular view.
> I can't do this manually because my DB contains 100's of stored procedures
> Thanks
> Kiran
>|||Hi Simon,
The "sp_depends view_name" returns the list of column names that are in the
view and the related object name, type etc.....
I want to get the names of object that uses this View.
For eg. I have a view vew_employees. There are 4 stored procedures that uses
this view vew_employees.
I need something that will return me the names of these 4 sp.s
Thanks
Kiran
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> you can check the dependancies in EM, or in query analyzer use sp_depends
> sp_depends
> Displays information about database object dependencies (for example, the
> views and procedures that depend on a table or view, and the tables and
> views that are depended on by the view or procedure). References to
> objects
> outside the current database are not reported.
> Syntax
> sp_depends [ @.objname = ] 'object'
> Arguments
> [@.objname =] 'object'
> The database object to examine for dependencies. The object can be a
> table,
> view, stored procedure, or trigger. object is nvarchar(776), with no
> default.
> Return Code Values
> 0 (success) or 1 (failure)
>
> "Kiran Kumar" <nospam@.spam.com> wrote in message
> news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> procedure)
>|||Assuming your view name is somewhat unique, this will give you an idea:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%view_name%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
sp_depends[vbcol=seagreen]
the[vbcol=seagreen]
>|||But it does return those 4 procs - within the name column of the output. Is
this not what you are looking for?
In enterprise manager, right click the view, go to "all tasks", and click
"display dependancies...". This will show you the same thing
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
sp_depends[vbcol=seagreen]
the[vbcol=seagreen]
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>
|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||I discussed this at:
http://vyaskn.tripod.com/sql_server_...edure_code.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||I discussed this at:
http://vyaskn.tripod.com/sql_server...cedure_code.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||I discussed this at:
http://vyaskn.tripod.com/sql_server...cedure_code.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:

> Hi, I would like to find all the cases within all the stored procedures in a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>
|||http://www.aspfaq.com/2037
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
|||I discussed this at:
http://vyaskn.tripod.com/sql_server_...edure_code.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>

Find / Search for a string in stored procedure?

Hi, I would like to find all the cases within all the stored procedures in a
database where a particular word exists.
Any suggestions? Thanks in advance.SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%YourWord%'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||Try,
use northwind
go
declare @.s varchar(25)
set @.s = 'orders'
select distinct
object_name([id])
from
syscomments
where
objectproperty([id], 'IsProcedure') = 1
and patindex('%' + @.s + '%', [text]) > 0
go
AMB
"rh" wrote:
> Hi, I would like to find all the cases within all the stored procedures in a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>
>|||http://www.aspfaq.com/2037
--
http://www.aspfaq.com/
(Reverse address to reply.)
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>|||I discussed this at:
http://vyaskn.tripod.com/sql_server_search_stored_procedure_code.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rh" <rherrera@.smci.com> wrote in message
news:OCKPvd1HFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi, I would like to find all the cases within all the stored procedures in
a
> database where a particular word exists.
> Any suggestions? Thanks in advance.
>