finding minimum value

How to find the minimum value from time Timestamp column ?

I want get the eralier timestamp vlaues from the avaliable list

when iam using Aggregate transformation ..its again giving all the list of vlaues

Thanks
Niru

If you have only one minimum per data set I would use the Script component to get it manually.

Thanks.

|||no i have lot of set of duplicate values with in the same column....I have to get the Minimum ones in those sets ..like

5
5
3
3

Desired result:

5
5

|||

I still do not understand your scenario.

If you have a data like this:

A B

1 1

1 2

2 7

2 8

You can use the Aggregate component to group on column A and find a minimum on B, so it would produce:

A B

1 1

2 7

Is that what you are looking for?

Finding Max Length of ntext column in SP

I have upsized an access database and am trying to find all the maximum
lengths of the columns, so I can restructure the DB. I have the query that
will give me the max length of the column, but since I have a bunch of
columns, I would like to write a SP to do it for all the columns.
Here is my query
SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
FROM tblIPPCore2
ORDER BY MembersLenMax DESC;
How can I automate this query?
Thanks,
DrewI have modified my query to the following,
SELECT MAX(DATALENGTH([Members Present])) AS MembersPresentLenMax
FROM tblIPPCore2
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OP4nSJvVFHA.1508@.tk2msftngp13.phx.gbl...
>I have upsized an access database and am trying to find all the maximum
>lengths of the columns, so I can restructure the DB. I have the query that
>will give me the max length of the column, but since I have a bunch of
>columns, I would like to write a SP to do it for all the columns.
> Here is my query
> SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
> FROM tblIPPCore2
> ORDER BY MembersLenMax DESC;
> How can I automate this query?
> Thanks,
> Drew
>|||Here is the answer...
This Procedure takes @.TableName, and returns the maximum lengths for each
column in the table. There is no error correction, so column names must not
have spaces or weird characters.
CREATE PROCEDURE spMaxLen
@.TableName varchar(20)
AS
declare @.v_tempTable TABLE (myid bigint identity(1,1), sSQL varchar(1000))
DECLARE @.vMaxCount int, @.iCount int, @.sSQL varchar(1000)
INSERT into @.v_tempTable (sSQL)
SELECT 'SELECT MAX(DATALENGTH([' + column_name + '])) AS ' +
replace(column_name, ' ', '') + 'MaxLen FROM ' + @.TableName
FROM information_schema.columns
where table_name = @.TableName
SET @.vMaxCount =@.@.ROWCOUNT
set @.iCount = 1
WHILE @.iCount < @.vMaxCount + 1
BEGIN
SELECT @.sSQL = sSQL from @.v_tempTable where myid = @.iCount
exec (@.ssql)
SET @.iCount = @.iCount + 1
END
GO
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:OP4nSJvVFHA.1508@.tk2msftngp13.phx.gbl...
>I have upsized an access database and am trying to find all the maximum
>lengths of the columns, so I can restructure the DB. I have the query that
>will give me the max length of the column, but since I have a bunch of
>columns, I would like to write a SP to do it for all the columns.
> Here is my query
> SELECT TOP 1 DATALENGTH([Members Present]) AS MembersLenMax
> FROM tblIPPCore2
> ORDER BY MembersLenMax DESC;
> How can I automate this query?
> Thanks,
> Drew
>

finding lower case data

I have a table with a VIN number column, I need to find all entries where th
e
VIN contains a lower case character, example '4d0131P2X4E146985'. This recor
d
should be included in the results because it contains 'd'.. What would be th
e
easiest way to write a query to return these, can I use regular expression i
n
T-SQL...?
Thanks,
DanAlien2_51,
Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the column
in question. I guess it is using a case insensitive one. Use the COLLATE
clause to tell SQL Server which collation to use during the operation and us
e
a case sensitive one. You can also use function binary_checksum.
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where binary_checksum(c1) != binary_checksum(upper(c1))
AMB
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan|||You could do this:
select VIN
from yourTable
where VIN like '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS
-- or
select VIN
from yourTable
where VIN like '%[a-z]%'
collate Latin1_General_BIN
Note that it will not work to use the range [a-z] in the first
example, because in the CS_AS collation, [a-z] contains the
uppercase letters A through Y.
Steve Kass
Drew University
http://www.stevekass.com
Alien2_51 wrote:

>I have a table with a VIN number column, I need to find all entries where t
he
>VIN contains a lower case character, example '4d0131P2X4E146985'. This reco
rd
>should be included in the results because it contains 'd'.. What would be t
he
>easiest way to write a query to return these, can I use regular expression
in
>T-SQL...?
>Thanks,
>Dan
>|||Thanks Alejandro... Just what I needed, and so simple...
DB
"Alejandro Mesa" wrote:
> Alien2_51,
> Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the colu
mn
> in question. I guess it is using a case insensitive one. Use the COLLATE
> clause to tell SQL Server which collation to use during the operation and
use
> a case sensitive one. You can also use function binary_checksum.
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where binary_checksum(c1) != binary_checksum(upper(c1))
>
> AMB
>
> "Alien2_51" wrote:
>|||Alien, I guess you might want to check this out
http://vadivel.blogspot.com/2003/12...e-searches.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan

Finding illegal characters

I'm trying to find all occurances of bad data in a varchar column. The bad
characters include: /";:<>|[],.'?~`!$%^&*()-+={}\
I need a quick and dirty way to do this.
I tried:
--Doesn't work...
WHERE MyColumn LIKE '%[/";:<>|[],.''?~`!$%^&*()-+={}\]%'
--Works, but too much code.
WHERE MyColumn like '%[|]%' OR MyColumn LIKE '%[[]%' OR ....
Ideas Anyone?It might be better to identify and define the characters you want to allow,
rather than the opposite.
What's nice about this approach is thatyou can indicate A-Z, a-z and 0-9 in
a range. Just add any other allowable characters to the end.
CREATE TABLE #foo
(
bar VARCHAR(32)
)
INSERT #foo(bar) SELECT 'legal';
INSERT #foo(bar) SELECT 'not legal -- ~?@.#@.#';
-- find the legal ones
SELECT * FROM #foo
WHERE PATINDEX('%[^A-Za-z0-9]%', bar) = 0
-- find the bad ones
SELECT * FROM #foo
WHERE PATINDEX('%[^A-Za-z0-9]%', bar) > 0
DROP TABLE #foo
"Billg_sd" <Billgsd@.discussions.microsoft.com> wrote in message
news:85320AA5-EA1F-4C3E-A4BB-CB85ECA6D17D@.microsoft.com...
> I'm trying to find all occurances of bad data in a varchar column. The
> bad
> characters include: /";:<>|[],.'?~`!$%^&*()-+={}\
> I need a quick and dirty way to do this.
> I tried:
> --Doesn't work...
> WHERE MyColumn LIKE '%[/";:<>|[],.''?~`!$%^&*()-+={}\]%'
> --Works, but too much code.
> WHERE MyColumn like '%[|]%' OR MyColumn LIKE '%[[]%' OR ....
> Ideas Anyone?|||Billg_sd (Billgsd@.discussions.microsoft.com) writes:
> I'm trying to find all occurances of bad data in a varchar column. The
> bad characters include: /";:<>|[],.'?~`!$%^&*()-+={}\
> I need a quick and dirty way to do this.
> I tried:
> --Doesn't work...
> WHERE MyColumn LIKE '%[/";:<>|[],.''?~`!$%^&*()-+={}\]%'
> --Works, but too much code.
> WHERE MyColumn like '%[|]%' OR MyColumn LIKE '%[[]%' OR ....
Here's variation that works:
WHERE a LIKE '%[-[/";:<>|,.''?~`!$%^&*()+={}\]%' OR a like '%]%'
There were two problems in the original string:
1) The hyphen must come first, or else it denotes a range.
2) The right bracket must be tested for separately.
As Aaron says, it may be better to test for permitted characters instead,
but that may be devilish too. For instance, his example was:
'%[^A-Za-z0-9]%'
But the result of this range depends on the collation. For instance,
in a Finnish-Swedish collation it would match the word "coperative",
whereas it would not in many other collations.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

finding foreign key info

Hi,
Is there a way to find out if a column on a table is a foreign key, what
table it references and what column on that table it is referencing. All of
my foreign keys are a single column, which should make things easier.
Any help would be greatly appreciated.
Regards,
NedYou can get this from a couple of INFORMATION_SCHEMA views, e.g.
select
pk_ccu.table_name as PK_Table,
pk_ccu.column_name as PK_Column,
fk_ccu.table_name as FK_Table,
fk_ccu.column_name as FK_Column
from
information_schema.constraint_column_usage pk_ccu
join information_schema.referential_constraints rc on
pk_ccu.constraint_name=rc.unique_constraint_name
join information_schema.constraint_column_usage fk_ccu on
rc.constraint_name=fk_ccu.constraint_name
where
fk_ccu.table_Name='table_in_question'
and fk_ccu.column_name='column_in_question'
Ned wrote:
> Hi,
> Is there a way to find out if a column on a table is a foreign key, what
> table it references and what column on that table it is referencing. All
of
> my foreign keys are a single column, which should make things easier.
> Any help would be greatly appreciated.
> Regards,
> Ned
>|||Thanks Trey,
Worked like a charm.
Regards,
Ned
"Trey Walpole" <treyNOpole@.comSPAMcast.net> wrote in message
news:exXjWn0rFHA.460@.TK2MSFTNGP15.phx.gbl...
> You can get this from a couple of INFORMATION_SCHEMA views, e.g.
> select
> pk_ccu.table_name as PK_Table,
> pk_ccu.column_name as PK_Column,
> fk_ccu.table_name as FK_Table,
> fk_ccu.column_name as FK_Column
> from
> information_schema.constraint_column_usage pk_ccu
> join information_schema.referential_constraints rc on
> pk_ccu.constraint_name=rc.unique_constraint_name
> join information_schema.constraint_column_usage fk_ccu on
> rc.constraint_name=fk_ccu.constraint_name
> where
> fk_ccu.table_Name='table_in_question'
> and fk_ccu.column_name='column_in_question'
>
> Ned wrote:

Finding 'Error Column' Causing Error

Good Morning,

Am I new at this so please bear with me. I searched the site and found threads on identifying the meaning of an error code and have instituted that scripting. It is telling me that I have a 'The data value cannot be converted for reasons other than sign mismatch or data overflow.' error. It identifies the colum as 5301.

I searched the threads and found one telling me I could do an advanced editor search on the lineage id to find the column being referenced. I have traced the entire process using this information and cannot find a reference lineage id of 5301. Was that thread information accurate, and if so what do I do now? If not, can someone tell me how the heck I find column 5301? I cannot load my table from a flat file because of this.

Work so far:

I have checked for integrity between column definitions and source flat file. I applied derived column changes to make the data transform to the appropriate data type/order where necessary. This part works without error. (Or seems to, there is no error output from this piece.) It is only on the final attempt to load that the process errors with these messages.

Thank you in advance to anyone who can help me.

Rog

It should be, I think, the INPUT column lineage ID of the offending component.|||

Tracing input columns I can find a lineage ID of 5103 NOT 5301. There is no reference in any of the ID fields, including the lineage, of 5301. Any other ideas?

|||Edit the .dtsx package file in your favorite text editor and look for 5301. Look around there a bit to see what column it references.|||

Well it turns out that worked. The column id and lineage ID are not related at all to each other. (See below.) It would seem to me there should be a better way to find this information out than this very cumbersome process.

<inputColumn id="5301" name="" description="" lineageId="5109"

|||

CaptainMyCaptain wrote:

Well it turns out that worked. The column id and lineage ID are not related at all to each other. (See below.) It would seem to me there should be a better way to find this information out than this very cumbersome process.

<inputColumn id="5301" name="" description="" lineageId="5109"

Right, well, there is. You should be able to find the INPUT column of 5301 in your data flow when looking at the advanced editor for any given component.

The lineage ID, 5109, in this case tells me that this column has gone through some transformations along the way, but it was originally 5109 somewhere along the line. If you look back a ways, you should find an id="5109" on column further up in the .dtsx source. That is this column's "parent."

Finding duplicate records using two columns

I'm trying to find duplicate records in a table. I know how to do that using
a single column value, but I need to do it using the combined values of two
columns, company_id and client_id. There multiple are records with the same
company_id value (e.g., 123) and multiple records with the same department_id
value (e.g., 456). But there should be only one record with a company_id
value of 123 and a department_id value of 456.
I've searched for data in one table that is not in another table on these
two columns using CAST to concatenate the values:
select * from company c
where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
not in
(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
from client r)
But I can't seem to figure out how to use CAST to find duplicate records in
a single table. Should I be doing this a different way?
Any and all help would be appreciated.
JohnSELECT company_id, client_id, count(*) as Duplicates
FROM Company
GROUP BY company_id, client_id
HAVING COUNT(*) > 1
This works great with one column or ten columns.
Roy Harvey
Beacon Falls, CT
On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
<moderndads(nospam)@.hotmail.com> wrote:
>I'm trying to find duplicate records in a table. I know how to do that using
>a single column value, but I need to do it using the combined values of two
>columns, company_id and client_id. There multiple are records with the same
>company_id value (e.g., 123) and multiple records with the same department_id
>value (e.g., 456). But there should be only one record with a company_id
>value of 123 and a department_id value of 456.
>I've searched for data in one table that is not in another table on these
>two columns using CAST to concatenate the values:
>select * from company c
>where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
>not in
>(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
>from client r)
>But I can't seem to figure out how to use CAST to find duplicate records in
>a single table. Should I be doing this a different way?
>Any and all help would be appreciated.
>John|||Thank you, Roy! It worked perfectly.
John
"Roy Harvey (SQL Server MVP)" wrote:
> SELECT company_id, client_id, count(*) as Duplicates
> FROM Company
> GROUP BY company_id, client_id
> HAVING COUNT(*) > 1
> This works great with one column or ten columns.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
> <moderndads(nospam)@.hotmail.com> wrote:
> >I'm trying to find duplicate records in a table. I know how to do that using
> >a single column value, but I need to do it using the combined values of two
> >columns, company_id and client_id. There multiple are records with the same
> >company_id value (e.g., 123) and multiple records with the same department_id
> >value (e.g., 456). But there should be only one record with a company_id
> >value of 123 and a department_id value of 456.
> >
> >I've searched for data in one table that is not in another table on these
> >two columns using CAST to concatenate the values:
> >
> >select * from company c
> >where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
> >not in
> >(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
> >from client r)
> >
> >But I can't seem to figure out how to use CAST to find duplicate records in
> >a single table. Should I be doing this a different way?
> >
> >Any and all help would be appreciated.
> >
> >John
>

Finding Duplicate Foreign Keys

Hi

i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column

select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc

but i am not able to get the fks created more than once on same column refering to same pk

Thanks in AdvanceIs this helpful?

Field1 has the following values;
1, 2, 3, 3, 4, 5, 6, 6, 6, 7, 8, 9
duplicates highlighted in bold

The following SQL will pick out duplicates only

SELECT Field1
FROM MyTable
GROUP BY Field1
HAVING Count(*) > 1

Field1
--
3
6

:cool:|||I am not sure what is the exact problem, but I think 'Information_Schema' might help. It contains number of views that can be used to retrive meta data.

E.g.
Select * from information_Schema.TABLE_CONSTRAINTS|||here's one that I use in SqlSpec, it may be more than you need though:

select
quotename(su2.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so2.name) collate Latin1_General_CI_AS as parent
,quotename(su1.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so1.name) collate Latin1_General_CI_AS as name
,so1.name as shortname ,sc2.name as colName
,'foreign key from ' + quotename(su2.name) + N'.' + quotename(so2.name) + ' to ' + quotename(su3.name) + N'.' + quotename(so3.name) as description
,quotename(su3.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so3.name) collate Latin1_General_CI_AS as refTable
,sc3.name as refColumn
,ObjectProperty(sf.constid, 'CnstIsDisabled') as isDisabled
,ObjectProperty(sf.constid, 'CnstIsNotRepl') as isNotForReplication
,ObjectProperty(sf.constid, 'CnstIsDeleteCascade') as isDeleteCascade
,ObjectProperty(sf.constid, 'CnstIsUpdateCascade') as isUpdateCascade
,so1.object_id as id from sysforeignkeys sf
join sys.objects so1 on sf.constid=so1.object_id
join sys.objects so2 on sf.fkeyid=so2.object_id
join sys.objects so3 on sf.rkeyid=so3.object_id
join sys.schemas su1 on su1.schema_id=so1.schema_id
join sys.schemas su2 on su2.schema_id=so2.schema_id
join sys.schemas su3 on su3.schema_id=so3.schema_id
join syscolumns sc2 on sc2.colid=sf.fkey and sc2.id=so2.object_id
join syscolumns sc3 on sc3.colid=sf.rkey and sc3.id=so3.object_id|||Thanks You ALL for your reply.

i got that using
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

with CTEsql

Finding date using PATINDEX in varchar

Hi:
One of the columns in my table is a notes column i.e everytime a user
updates it the date,time, users name and his/her note gets appended in
front. The notes column has text in following format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
I am supposed to filter out the data by each user,its date and time i.e
8/4/2006 Linda King Per MDCR no clm on file,reblld.
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
Any suggestions are welcomed.Hi
DECLARE @.st VARCHAR(100)
SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
SELECT
SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
<sfazili@.gmail.com> wrote in message
news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> Hi:
> One of the columns in my table is a notes column i.e everytime a user
> updates it the date,time, users name and his/her note gets appended in
> front. The notes column has text in following format:
> 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> I am supposed to filter out the data by each user,its date and time i.e
> 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> Any suggestions are welcomed.
>|||Hi Uri:
Thanks for ur reply. Actually the notes field has text in the follwoing
format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
Each update to the notes field just get appended to the field in front.
Firstly, I need to separate each individual update..cant use space as
an identifier... need help here. The thing is that after each
indiviaudal note is identified it should be in follwoing format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
After this need to separate each individaul note into thre columns:
8/4/2006 Linda King Per MDCR/ no clm on file,reblld
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22
3/30/2006 Maria James Patient asks to follow -MEDICARE
Any suggestions appreciated
Uri Dimant wrote:
> Hi
> DECLARE @.st VARCHAR(100)
> SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
> SELECT
> SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
> CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
> SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
>
>
> <sfazili@.gmail.com> wrote in message
> news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> > Hi:
> >
> > One of the columns in my table is a notes column i.e everytime a user
> > updates it the date,time, users name and his/her note gets appended in
> > front. The notes column has text in following format:
> >
> > 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> > 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> >
> > I am supposed to filter out the data by each user,its date and time i.e
> > 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> > 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> >
> > Any suggestions are welcomed.
> >

Finding dashes/hyphens in a column.

Hi,

I'm having trouble running the following query:

select * from message where text_body like ' ----%'

ie, five spaces followed by at least ten hyphens. The query doesn't
complete, so eventually I cancel it. If I remove the hyphens from the
query ("... like ' %'") then it runs fine (though it doesn't find
the correct data).

Am I confusing SQL Server by using a wildcard or regular expression?
(I'm using SQL Server 2000 - 8.99.760).
Thanks in advance for any help

RichardOn 19 Jan 2005 07:46:49 -0800, richard_thomas@.bigfoot.com wrote:

>I'm having trouble running the following query:
>select * from message where text_body like ' ----%'
>ie, five spaces followed by at least ten hyphens. The query doesn't
>complete, so eventually I cancel it. If I remove the hyphens from the
>query ("... like ' %'") then it runs fine (though it doesn't find
>the correct data).
>Am I confusing SQL Server by using a wildcard or regular expression?
>(I'm using SQL Server 2000 - 8.99.760).
>Thanks in advance for any help

Hi Richard,

I couldn't reproduce this behaviour. The following script returns exactly
the one row I expect to be returned:

create table message (text_body varchar(50))
go
insert message values (' ---- should be returned')
insert message values (' --- one dash short')
insert message values (' ---- one space short')
insert message values (' ---- one space too much')
go
select * from message where text_body like ' ----%'
go
drop table message
go

Can you post a script that will reproduce the buggy behaviour on my
machine?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(richard_thomas@.bigfoot.com) writes:
> I'm having trouble running the following query:
> select * from message where text_body like ' ----%'
> ie, five spaces followed by at least ten hyphens. The query doesn't
> complete, so eventually I cancel it. If I remove the hyphens from the
> query ("... like ' %'") then it runs fine (though it doesn't find
> the correct data).

How many rows are there in message? What query plans do you get in
the two cases?

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

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

I'm beginning to have a feeling that the 'spaces' aren't in fact
spaces.

By doing this, it seems to work:

select * from message where text_body like '____---%'

but also finds strings such as

'----'
ie the first 4 characters are hyphens rather than dashes.

Is there a wildcard that matches whitespace?
Thanks again for your help!|||Sorry, that should read
"The first 4 characters are hyphens rather than spaces" (not dashes!)|||On 20 Jan 2005 02:21:21 -0800, richard_thomas@.bigfoot.com wrote:

>Is there a wildcard that matches whitespace?

Hi Richard,

Unfortunately, no.

You should locate rows that you suspect to have non-space whitespace,
using (e.g.)
WHEREtext_body like '____---%'
ANDtext_body NOT like ' ---%'

Then, inspect the real contents of text_body in such rows:
SELECT ASCII (SUBSTRING (text_body, 1, 1)),
ASCII (SUBSTRING (text_body, 2, 1)),
ASCII (SUBSTRING (text_body, 3, 1)),
ASCII (SUBSTRING (text_body, 4, 1))
FROM ...
WHERE ...

Once you know that, you can start thinking how to match all variations you
may have in your data.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >=
@.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
Kieran
Kidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:

>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=
>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
>) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >=
@.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
KieranKidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:

>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=
>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
> ) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >= @.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
KieranKidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:
>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
>) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>sql

Finding columns from sysindexes.

I understand that in the "Keys" column in the "sysindexes" table, the columns are stored in Binary format. Is there any way by which I can get these column names by deciphering sysindexes.keys ? I don't want to use index_col() function.Use sysindexes, syscolumns, sysindexkeys. See BOL for details.|||Thanks a lot. I used your suggestion and got the results. I had forgotten about the sysindexkeys table initially. Thanks again. --Suresh.

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 column use with syscomments

In SQL 2000, I know that "Display Dependencies" (and probably the
Sysdepends table) are not accurate.
Does the syscomments field for a view always contain the correct and
current view definition?
I need to find all uses of a given field across all views (there are about
150 views). Is looking in syscomments, or Information_Schema, going to be
reliable? Are there better ways? I have looked in Google and haven't
found anything yet. Still looking though...
Thanks.
David WalkerYou can refresh all your views and then use system views
information_schema.columns.
Example:
use northwind
go
declare @.ts sysname
declare @.tn sysname
declare @.sql nvarchar(4000)
declare views_cursor cursor local fast_forward
for
select
table_schema,
table_name
from
information_schema.tables
where
table_type = 'view'
and objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsMSShipped') = 0
open views_cursor
while 1 = 1
begin
fetch next from views_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'exec sp_refreshview ''' + quotename(@.ts) + N'.' +
quotename(@.tn) + ''''
exec sp_executesql @.sql
end
close views_cursor
deallocate views_cursor
declare @.cn sysname
set @.cn = 'OrderID'
select
*
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsView') = 1
and objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_name = @.cn
order by
table_schema,
table_name,
ordinal_position
go
AMB
"DWalker" wrote:

> In SQL 2000, I know that "Display Dependencies" (and probably the
> Sysdepends table) are not accurate.
> Does the syscomments field for a view always contain the correct and
> current view definition?
> I need to find all uses of a given field across all views (there are about
> 150 views). Is looking in syscomments, or Information_Schema, going to be
> reliable? Are there better ways? I have looked in Google and haven't
> found anything yet. Still looking though...
> Thanks.
> David Walker
>|||> Does the syscomments field for a view always contain the correct and
> current view definition?
AFAIK, yes. With one exception. If you use sp_rename to rename a view, the i
nfo in syscomments will
have the old name (in the CREATE VIEW part).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DWalker" <none@.none.com> wrote in message news:eAQxymoSFHA.1384@.TK2MSFTNGP09.phx.gbl...[co
lor=darkred]
> In SQL 2000, I know that "Display Dependencies" (and probably the
> Sysdepends table) are not accurate.
> Does the syscomments field for a view always contain the correct and
> current view definition?
> I need to find all uses of a given field across all views (there are about
> 150 views). Is looking in syscomments, or Information_Schema, going to be
> reliable? Are there better ways? I have looked in Google and haven't
> found anything yet. Still looking though...
> Thanks.
> David Walker[/color]|||And another one is that if you use "select * ..." then you will not find any
column name in the syscomments, but you will in the syscolumns. That is the
reason why after refreshing the view, I selected from
information_schema.columns and not from syscomments.
AMB
"Tibor Karaszi" wrote:

> AFAIK, yes. With one exception. If you use sp_rename to rename a view, the
info in syscomments will
> have the old name (in the CREATE VIEW part).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DWalker" <none@.none.com> wrote in message news:eAQxymoSFHA.1384@.TK2MSFTNG
P09.phx.gbl...
>
>|||"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:B0934023-3D8D-46DE-AA73-C27AC81C8F73@.microsoft.com:

> And another one is that if you use "select * ..." then you will not
> find any column name in the syscomments, but you will in the
> syscolumns. That is the reason why after refreshing the view, I
> selected from information_schema.columns and not from syscomments.
>
> AMB
>
Thanks to you both. I didn't know about refreshing the views. I'll
steal that code from you, AMB, and keep it in my database. There are
times when I want Display Dependencies in EM to give me the right
answer, and it looks like refreshing the views when I need this
information, is the way to go.
Thanks!
Davidsql

Finding and updating all rows with the same value in a column

This integer column should be unique, s? prior to altering it to unique i
need to traverse all rows and update the rows with the same value. This tabl
e
has another colun which is the primary key and when 2 rows has the same valu
e
the row with the highest value of the primary key will be updated.
Any easy T-SQL way to do it?
Regards,
Olavupdate your_table
set your_column = whatever you wish to set it to
where exists(select 1 from your_table yt
where your_table.your_column = yt.your_column
and your_table.PK_column > yt.PK_column)
Just curious: what kind of information do you have in that column so
that you can easily update it just to enforse uniqueness?sql

Finding all references to a column

I have a task to where I need to move a column from one table to
another. I want to be sure I update any view, stored procedure,
trigger, etc. that references the column. I simply want a query that
will report the related objects and then I will update them manually
but before I go and try and figure out how to do this by querying the
sys tables is there an sp_sproc that will do this?[posted and mailed, please reply in news]

rnewman (newmanr19@.yahoo.com) writes:
> I have a task to where I need to move a column from one table to
> another. I want to be sure I update any view, stored procedure,
> trigger, etc. that references the column. I simply want a query that
> will report the related objects and then I will update them manually
> but before I go and try and figure out how to do this by querying the
> sys tables is there an sp_sproc that will do this?

The best way is to build the database from scripts, with the column
reomved, and then look through all errors you get.

You can also run this query:

select object_name(id)
from sysdepends
where depid = object_id('tbl')
and col_name(depid, depnumber) = 'col'
order by 1

However, this may not be reliable. If you can be confident that all
procedures abd views have been created/altered after the table was
created, this will work. But if you have dropped the table and replaced
with a new version, or you loaded stored procedures before you created
the table, the dependency information will be incomplete.

Note: while the sysdepends tables is documented in Books Online,
the usage of the depnumber as column id is undocumented, and may
be subject to change without notice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

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_columns_in_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
>sql