finding a data in my db (700 tbl)

Hi
I have a db that contains ~700 tbl. I am looking for a data in my db.
I don t know which table and column i need to look in, bu i know the
data exist. Is there any way to search my whole db to find the data?
Thanks in advance
You can search the web and find a script that does this (kondredi may have
one). There was a recent thread on the same issue.
You can also roll your own using sysobjects (type = 'u') and syscolumns and
dynamically create a bunch of select statements with where clauses using
LIKE %searcheddata%. If you are searching for numeric values it is a bit
easier since you can do a straight =.
TheSQLGuru
President
Indicium Resources, Inc.
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegr oups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>
|||On May 22, 11:20 am, esen...@.gmail.com wrote:
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
My first thought is to use a cursor to loop through your tables using
this query:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Then using dynamic sql, write a query to find your data.
|||One way to approach this is to dump all the tables out via bcp to text files
and then search these text files. You can easily script to bcp the data out
of these 700 tables. I would bet this is easier and faster than an
in-database search if you absolutely have no idea where that piece of data
may be.
Linchi
"esengul@.gmail.com" wrote:

> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>
|||Hi
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegr oups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>

finding a data in my db (700 tbl)

Hi
I have a db that contains ~700 tbl. I am looking for a data in my db.
I don t know which table and column i need to look in, bu i know the
data exist. Is there any way to search my whole db to find the data?
Thanks in advanceYou can search the web and find a script that does this (kondredi may have
one). There was a recent thread on the same issue.
You can also roll your own using sysobjects (type = 'u') and syscolumns and
dynamically create a bunch of select statements with where clauses using
LIKE %searcheddata%. If you are searching for numeric values it is a bit
easier since you can do a straight =.
TheSQLGuru
President
Indicium Resources, Inc.
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegroups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>|||On May 22, 11:20 am, esen...@.gmail.com wrote:
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
My first thought is to use a cursor to loop through your tables using
this query:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Then using dynamic sql, write a query to find your data.|||One way to approach this is to dump all the tables out via bcp to text files
and then search these text files. You can easily script to bcp the data out
of these 700 tables. I would bet this is easier and faster than an
in-database search if you absolutely have no idea where that piece of data
may be.
Linchi
"esengul@.gmail.com" wrote:

> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>|||Hi
http://vyaskn.tripod.com/search_all..._all_tables.htm
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegroups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>

Find out how much data was replicated

Hi
I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
SP4. I've set up a transactional replication w/o updating
between two servers, one of which is a publisher and the
other is a distributor/pull-subscriber. I wonder if SQL
Server has means of finding out how much data was
replicated (i.e. sent to the subscriber), say, during the
day. If it hasn't, are there any third-party tools, which
would allow me to do this?
Many thanks,
Oskcheck the replication monitor in EM it will display a "gross" level of
information processed by article published.
"Osk" wrote:

> Hi
> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
> SP4. I've set up a transactional replication w/o updating
> between two servers, one of which is a publisher and the
> other is a distributor/pull-subscriber. I wonder if SQL
> Server has means of finding out how much data was
> replicated (i.e. sent to the subscriber), say, during the
> day. If it hasn't, are there any third-party tools, which
> would allow me to do this?
> --
> Many thanks,
> Osk
>|||Hi
What do you mean by "gross" level? I need to know how much
bytes were delivered to the subscriber during a time interval.
Thanks,
Osk

>--Original Message--
>check the replication monitor in EM it will display a
"gross" level of
>information processed by article published.
>"Osk" wrote:
>
>.
>

Find out how much data was replicated

Hi
I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
SP4. I've set up a transactional replication w/o updating
between two servers, one of which is a publisher and the
other is a distributor/pull-subscriber. I wonder if SQL
Server has means of finding out how much data was
replicated (i.e. sent to the subscriber), say, during the
day. If it hasn't, are there any third-party tools, which
would allow me to do this?
Many thanks,
Osk
check the replication monitor in EM it will display a "gross" level of
information processed by article published.
"Osk" wrote:

> Hi
> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
> SP4. I've set up a transactional replication w/o updating
> between two servers, one of which is a publisher and the
> other is a distributor/pull-subscriber. I wonder if SQL
> Server has means of finding out how much data was
> replicated (i.e. sent to the subscriber), say, during the
> day. If it hasn't, are there any third-party tools, which
> would allow me to do this?
> --
> Many thanks,
> Osk
>
|||Hi
What do you mean by "gross" level? I need to know how much
bytes were delivered to the subscriber during a time interval.
Thanks,
Osk

>--Original Message--
>check the replication monitor in EM it will display a
"gross" level of
>information processed by article published.
>"Osk" wrote:
>.
>

Find last char index

Hi
i have column with this values :
aa-bb-cc-dd
ss-aa
dd-aa-ee
How can i find last '-' char index
i need retrieve this result :
dd
aa
ee
Thank youSELECT RIGHT(col,CHARINDEX('-',REVERSE(col))-1)
FROM YourTable
David Portas
SQL Server MVP
--|||The las '-' is the first of the reverse. Try:
select
reverse(left(reverse(colA), charindex('-', reverse(colA)) - 1))
from
(
select 'aa-bb-cc-dd'
union all
select 'ss-aa'
union all
select 'dd-aa-ee'
) as t(colA)
go
-- if max number of dashes allowed is 4, then
select
parsename(replace(colA, '-', '.'), 1)
from
(
select 'aa-bb-cc-dd'
union all
select 'ss-aa'
union all
select 'dd-aa-ee'
) as t(colA)
go
AMB
"Itzik" wrote:

> Hi
> i have column with this values :
> aa-bb-cc-dd
> ss-aa
> dd-aa-ee
> How can i find last '-' char index
> i need retrieve this result :
> dd
> aa
> ee
> Thank you
>
>

Find a name of an index in deadlock situation.

Hi
I have a deadlock occurring.
Using MSSQL 2005 current activity lock screens I can see one process is
waiting for a lock to be released on a Database object. with objectId =
1441294802092032 and type = KEY - so I know I have a lock on an Index
and what table the index belongs to,
But how can I find the name of the index that is blocked?
This was part of the default view in SQL2000 activity manager. I cannot
use object_name (1441294802092032 ).
Pain is cannot even export the details of the locks per process to a
file.
Yitzak
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
"Yitzak" <terryshamir@.yahoo.co.uk> wrote in message
news:1164814916.178901.38620@.16g2000cwy.googlegrou ps.com...
> Hi
> I have a deadlock occurring.
> Using MSSQL 2005 current activity lock screens I can see one process is
> waiting for a lock to be released on a Database object. with objectId =
> 1441294802092032 and type = KEY - so I know I have a lock on an Index
> and what table the index belongs to,
> But how can I find the name of the index that is blocked?
> This was part of the default view in SQL2000 activity manager. I cannot
> use object_name (1441294802092032 ).
> Pain is cannot even export the details of the locks per process to a
> file.
>

Find a name of an index in deadlock situation.

Hi
I have a deadlock occurring.
Using MSSQL 2005 current activity lock screens I can see one process is
waiting for a lock to be released on a Database object. with objectId =
1441294802092032 and type = KEY - so I know I have a lock on an Index
and what table the index belongs to,
But how can I find the name of the index that is blocked'
This was part of the default view in SQL2000 activity manager. I cannot
use object_name (1441294802092032 ).
Pain is cannot even export the details of the locks per process to a
file.Yitzak
http://blogs.msdn.com/bartd/archive.../09/747119.aspx
"Yitzak" <terryshamir@.yahoo.co.uk> wrote in message
news:1164814916.178901.38620@.16g2000cwy.googlegroups.com...
> Hi
> I have a deadlock occurring.
> Using MSSQL 2005 current activity lock screens I can see one process is
> waiting for a lock to be released on a Database object. with objectId =
> 1441294802092032 and type = KEY - so I know I have a lock on an Index
> and what table the index belongs to,
> But how can I find the name of the index that is blocked'
> This was part of the default view in SQL2000 activity manager. I cannot
> use object_name (1441294802092032 ).
> Pain is cannot even export the details of the locks per process to a
> file.
>

Find a date that occurs every 2 days from a fixed date

Hi
I am trying to write a script that only sums the values in a column if the
date for the record occurs every 2 days after another date. The trouble is,
it is a recurring 2 day cycle. i.e Only sum the values if the record date is
every 2nd day from the first date.
eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
05/03, 07/04 etc. If the record does not occur every second day, it should
return 0.
I can use datediff to get the difference and I can cast it into a decimal
and divide by 2 but can't get it to validate in the statement.
Is there an easier way?Mark,
Post your DDL & T-SQL ?
Robert
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
> Hi
> I am trying to write a script that only sums the values in a column if the
> date for the record occurs every 2 days after another date. The trouble
> is,
> it is a recurring 2 day cycle. i.e Only sum the values if the record date
> is
> every 2nd day from the first date.
> eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
> 05/03, 07/04 etc. If the record does not occur every second day, it should
> return 0.
> I can use datediff to get the difference and I can cast it into a decimal
> and divide by 2 but can't get it to validate in the statement.
> Is there an easier way?
>|||Here is the short version of a complex query:
select guest.arrival, multirate.room_type, multirate.rate_date,
count (multirate.room_type)
from guest, multirate
where guest.property=multirate.property and guest.account=multirate.account
and
multirate.rate_date='xxxxx'
The problem is this:
Assume the guest.arrival='2006-03-01'
If the multirate.rate_date in the where statment is '2006-03-02', it must
ignore the record from the count because the datediff between 02/03 and 01/0
3
is not a multiple of 2.
If the multitrate.rate_date='2006-03-03', it must count the record into the
result because it occurs 2 days after the arrival.
The multirate.rate_date will be a variable passed to the statement, so the
query must be able to validate that the rate_date has a multiple of 2
difference between the rate_date and arrival.
Does this make sense?
"Robert Ellis" wrote:

> Mark,
> Post your DDL & T-SQL ?
> Robert
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
>
>|||Mark:
Does the following example help? There may be other ways to do it.
IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
CREATE TABLE blah
(
BlahGroup CHAR(2) NOT NULL,
FirstDate DATETIME NOT NULL,
SecondDate DATETIME NOT NULL
)
GO
SET DATEFORMAT YMD
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-02'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-06'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-07'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-06'
GO
SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
FROM blah b
WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2) =
(FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
GROUP BY b.BlahGroup
GO
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
> Here is the short version of a complex query:
> select guest.arrival, multirate.room_type, multirate.rate_date,
> count (multirate.room_type)
> from guest, multirate
> where guest.property=multirate.property and
> guest.account=multirate.account
> and
> multirate.rate_date='xxxxx'
> The problem is this:
> Assume the guest.arrival='2006-03-01'
> If the multirate.rate_date in the where statment is '2006-03-02', it must
> ignore the record from the count because the datediff between 02/03 and
> 01/03
> is not a multiple of 2.
> If the multitrate.rate_date='2006-03-03', it must count the record into
> the
> result because it occurs 2 days after the arrival.
> The multirate.rate_date will be a variable passed to the statement, so the
> query must be able to validate that the rate_date has a multiple of 2
> difference between the rate_date and arrival.
> Does this make sense?
> "Robert Ellis" wrote:
>|||Additionally:
obviously you would add something like:
AND (FirstDate = @.ParamDate)
to the 'WHERE' clause for your implementation...
cheers,
Robert
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>|||% modulus
argh
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>