Reverse Wildcard Searches Impossible?

I have researched newsgroups and the web very thoroughly and
unsuccessfully for a solution to what I believe is a very common
problem. I know it's easy to do wildcard match against data in DB
(using LIKE and "%" and "?").

But is it possible to match a concrete string against a database of
wildcarded data? ("%" and LIKE do not work). For example:

CREATE TABLE blacklist (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
pattern VARCHAR(255) NOT NULL
/* ... */
);

INSERT INTO blacklist (pattern) VALUES ('%foobar.com');

Is there a select query that would match address mars2.foobar.com
against this row?

Some people claim that the following query will work. I have tried it
and it's not true on either Oracle or SQL server.

SELECT * FROM blacklist WHERE 'mars2.foobar.com' LIKE pattern;

Some people suggest breaking up the blacklist table into N varchar
fields for each domain segment and then representing a wildcard
character as a NULL and use isNull to match it. This does work to an
extent. However, a) it seems really ugly, b) does not allow arbitrary
wildcarding (eg %mars%foobar.com), and c) this is something the DB
should do out of the box.

Please help! Humanity will be greatful as there's currently no
solution to this anywhere on newsgroups."Robert Brown" <robertbrown1971@.yahoo.com> wrote in message
news:240a4d09.0404301119.467bd1e1@.posting.google.c om...
> I have researched newsgroups and the web very thoroughly and
> unsuccessfully for a solution to what I believe is a very common
> problem. I know it's easy to do wildcard match against data in DB
> (using LIKE and "%" and "?").
> But is it possible to match a concrete string against a database of
> wildcarded data? ("%" and LIKE do not work). For example:
>
> CREATE TABLE blacklist (
> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
> pattern VARCHAR(255) NOT NULL
> /* ... */
> );
> INSERT INTO blacklist (pattern) VALUES ('%foobar.com');
> Is there a select query that would match address mars2.foobar.com
> against this row?
> Some people claim that the following query will work. I have tried it
> and it's not true on either Oracle or SQL server.
> SELECT * FROM blacklist WHERE 'mars2.foobar.com' LIKE pattern;
> Some people suggest breaking up the blacklist table into N varchar
> fields for each domain segment and then representing a wildcard
> character as a NULL and use isNull to match it. This does work to an
> extent. However, a) it seems really ugly, b) does not allow arbitrary
> wildcarding (eg %mars%foobar.com), and c) this is something the DB
> should do out of the box.
> Please help! Humanity will be greatful as there's currently no
> solution to this anywhere on newsgroups.

Works fine for me on Oracle:

C:\>sqlplus
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0

SQL> create table blacklist (pattern varchar2(255));

Table created

SQL> insert into blacklist (pattern) values ('%foobar.com');

1 row inserted

SQL> select * from blacklist where 'mars2.foobar.com' like pattern;

PATTERN
-----------------------
--
%foobar.com|||David Best (davebest@.usa.net) writes:
> Works fine for me on Oracle:
> C:\>sqlplus
> Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
> SQL> create table blacklist (pattern varchar2(255));
> Table created
> SQL> insert into blacklist (pattern) values ('%foobar.com');
> 1 row inserted
> SQL> select * from blacklist where 'mars2.foobar.com' like pattern;

And the same example (save the funny varchar2) works on MS SQL Server too.

And should work on about any DBMS, as this is core SQL.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94DC31A1B00CYazorman@.127.0.0.1...
> David Best (davebest@.usa.net) writes:
> > Works fine for me on Oracle:
> > C:\>sqlplus
> > Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
> > SQL> create table blacklist (pattern varchar2(255));
> > Table created
> > SQL> insert into blacklist (pattern) values ('%foobar.com');
> > 1 row inserted
> > SQL> select * from blacklist where 'mars2.foobar.com' like pattern;
> And the same example (save the funny varchar2) works on MS SQL Server too.
> And should work on about any DBMS, as this is core SQL.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

It should work, but the query is going to be very inefficient.
Jim|||Jim Kennedy (kennedy-downwithspammersfamily@.attbi.net) writes:
> It should work, but the query is going to be very inefficient.

Yes, if there is an index on pattern it is not going to be useful,
since the match is at the end of the string. But that is not really
the same that it is very ineffecient. If you have a million entries,
you will certainly notice the toll. But with thousand? Not very much.
And thousand is a more likely number than a million.

For this particular case there exists a possible way to speed things up.
Since we search for the end of the string, you could have:

CREATE TABLE blacklist (pattern varchar(225) NOT NULL PRIMARY KEY,
revpattern AS reverse(pattern));
CREATE UNIQUE INDEX revix ON blacklist (revpattern);
go
INSERT blacklist VALUES ('%@.example.com')
go
SELECT pattern FROM blacklist
WHERE reverse('spammer@.example.com') LIKE revpattern

However computed columns is not standard SQL, and may not work on all
DBMSs. The above works in SQL Server.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The query will still need a table (or index) scan, because the column is
to the right of the LIKE operator...

Gert-Jan

Erland Sommarskog wrote:
> Jim Kennedy (kennedy-downwithspammersfamily@.attbi.net) writes:
> > It should work, but the query is going to be very inefficient.
> Yes, if there is an index on pattern it is not going to be useful,
> since the match is at the end of the string. But that is not really
> the same that it is very ineffecient. If you have a million entries,
> you will certainly notice the toll. But with thousand? Not very much.
> And thousand is a more likely number than a million.
> For this particular case there exists a possible way to speed things up.
> Since we search for the end of the string, you could have:
> CREATE TABLE blacklist (pattern varchar(225) NOT NULL PRIMARY KEY,
> revpattern AS reverse(pattern));
> CREATE UNIQUE INDEX revix ON blacklist (revpattern);
> go
> INSERT blacklist VALUES ('%@.example.com')
> go
> SELECT pattern FROM blacklist
> WHERE reverse('spammer@.example.com') LIKE revpattern
> However computed columns is not standard SQL, and may not work on all
> DBMSs. The above works in SQL Server.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

--
(Please reply only to the newsgroup)

Reverse Sign for Display

In Crystal Reports, there was a "reverse sign for display" property that could be checked for a number box. This was used for debits and credits (and such) so a negative number would be displayed as a positive and a positive displayed as a negative.

How would this work, or is there something simple, in Reporting Services?

Thanks for the information.

Just a simple way to implement this is =iif(fields!abc.value<0, abs(fields!abc.value), - & fields!abc.value)

|||

Use something like this for the value of the field:

=Fields!myField.Value * -1

That seems to simple. Is that what you're asking for?

|||Thank you both for your help. I knew it had to be a simple answer.

reverse order of hex bytes

is there a function in sql server to select the REVERSE order of a 32bit hex
value?
then i also need to convert each byte to a decimal number.
any help is much appreciated..
tia,
jtCan you post an example of what you are trying to accomplish?
If I understood your question right, it is not too hard to write a simple
function to change the order of hexdigits. In SQL 2000, there is an
undocumented proc xp_varbintohexstr, which you can use in a procedural loop
& get this done as well.
Anith|||Take a look at this example:
http://milambda.blogspot.com/2005/0...a.blogspot.com/|||>> is there a function in SQL Sserver to select the REVERSE order of a 32-bi
t hex value? <<
Why are you doing low level bit manipulation in SQL? This is like
driving screws wirh a brick. What are you trying to do? What is your
data model? Surely it is NOT at that physical level!!|||The benefit of a database is not just for storage but its ability todo
efficient data manipulation.
You seem to be stuck in the 70's store and retreieve model, client/server
techniques have passed you by!
I have a client who uses SQL Server to store GBytes of technical data and
requires the ability to do conversion like this to produce taylor data
exports for his own clients; the question - does he program the export in a
3gl or more easily using T-SQL (what its designed for) and binary
manipulation on the SELECT statement?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137469420.061461.205740@.g47g2000cwa.googlegroups.com...
> Why are you doing low level bit manipulation in SQL? This is like
> driving screws wirh a brick. What are you trying to do? What is your
> data model? Surely it is NOT at that physical level!!
>

reverse only the text parts of the field

i have to import addresses from an text file.
the text is reversed but numbers are not reversed
"abcd 12 def" comes as "dcba 12 fed"
if i use the reverse function it reveses also the number
and the it becomes "abcd 21 def"
how i can reverse only the text parts of the field?
thanksChop the parts of the string, cehck every single string for ISNUMERIC and
REVERSE it if it evaluates to false, then put the string together in a new
string.
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sam" wrote:

> i have to import addresses from an text file.
> the text is reversed but numbers are not reversed
> "abcd 12 def" comes as "dcba 12 fed"
> if i use the reverse function it reveses also the number
> and the it becomes "abcd 21 def"
> how i can reverse only the text parts of the field?
> thanks
>
>|||how to Chop the parts of the string?
"Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:9044AF5F-E604-4EEC-974C-A2CB06AE5756@.microsoft.com...
> Chop the parts of the string, cehck every single string for ISNUMERIC and
> REVERSE it if it evaluates to false, then put the string together in a new
> string.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Sam" wrote:
>|||Loomk for PATINDEX() in bol
DECLARE @.S Varchar(100)
SET @.s='Some text 123 text'
SELECT PATINDEX('%[0-9]%',@.s)
SELECT LEFT(@.s, PATINDEX('%[0-9]%',@.s)-1)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sam" <focus10@.zahav.net.il> wrote in message
news:eYbeeAamFHA.268@.TK2MSFTNGP10.phx.gbl...
> how to Chop the parts of the string?
> "Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message
> news:9044AF5F-E604-4EEC-974C-A2CB06AE5756@.microsoft.com...
>|||Try this.. should handle all the cases...
/*
ddl
Create table TestRev (Id int, Name Varchar(255))
Insert into TestRev values (1, 'abc 123 def 256')
Insert into TestRev values (2, 'olleh 321 dlrow 987')
Insert into TestRev values (3, 'abc 123456789 reversethis 256')
Insert into TestRev values (4, 'sqlserver 2000 or yukon')
*/
set nocount on
declare @.str Varchar(255)
declare @.id char(15)
declare @.strRev Varchar(255)
declare @.word Varchar(255)
declare @.newWord Varchar(255)
declare @.len int
declare @.pos int
Declare Cur_Rev Cursor
For select id, name from TestRev
open Cur_Rev
Fetch next from Cur_Rev into @.id, @.str
while @.@.fetch_status = 0
begin
set @.str = @.str + ' '
set @.strRev = @.str
set @.len = LEN(@.str)
while (@.len > 1)
begin
set @.pos = PATINDEX('% %',@.str)
set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
if IsNumeric(@.word) <> 1
Begin
set @.newword = reverse(@.word)
set @.strRev = Replace(@.strRev, @.word, @.newWord)
End
set @.str = substring(@.str, @.pos+1, @.len)
set @.len = len(@.str)
if @.pos = 0
break
end
select @.strRev 'Reversed'
fetch next from Cur_Rev into @.id, @.str
set @.strRev = @.str
end
Close Cur_Rev
Deallocate Cur_Rev
go
Thanks,
Prad
"Sam" <focus10@.zahav.net.il> wrote in message
news:eIlxouZmFHA.3648@.TK2MSFTNGP10.phx.gbl...
>i have to import addresses from an text file.
> the text is reversed but numbers are not reversed
> "abcd 12 def" comes as "dcba 12 fed"
> if i use the reverse function it reveses also the number
> and the it becomes "abcd 21 def"
> how i can reverse only the text parts of the field?
> thanks
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:544016
Pradeep Kutty wrote:
> Try this.. should handle all the cases...
> /*
> ddl
> Create table TestRev (Id int, Name Varchar(255))
> Insert into TestRev values (1, 'abc 123 def 256')
> Insert into TestRev values (2, 'olleh 321 dlrow 987')
> Insert into TestRev values (3, 'abc 123456789 reversethis 256')
> Insert into TestRev values (4, 'sqlserver 2000 or yukon')
> */
>
> set nocount on
> declare @.str Varchar(255)
> declare @.id char(15)
> declare @.strRev Varchar(255)
> declare @.word Varchar(255)
> declare @.newWord Varchar(255)
> declare @.len int
> declare @.pos int
>
> Declare Cur_Rev Cursor
> For select id, name from TestRev
>
> open Cur_Rev
> Fetch next from Cur_Rev into @.id, @.str
> while @.@.fetch_status = 0
> begin
> set @.str = @.str + ' '
> set @.strRev = @.str
> set @.len = LEN(@.str)
> while (@.len > 1)
> begin
> set @.pos = PATINDEX('% %',@.str)
> set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
> if IsNumeric(@.word) <> 1
> Begin
> set @.newword = reverse(@.word)
> set @.strRev = Replace(@.strRev, @.word, @.newWord)
> End
> set @.str = substring(@.str, @.pos+1, @.len)
> set @.len = len(@.str)
> if @.pos = 0
> break
> end
> select @.strRev 'Reversed'
> fetch next from Cur_Rev into @.id, @.str
> set @.strRev = @.str
> end
> Close Cur_Rev
> Deallocate Cur_Rev
> go
>
> Thanks,
> Prad
>
Doesn't work for if the string contains a string and it's reverse:
insert into TestRev values (5,'abc cba abc cba')
and run again (I get) 'abc abc abc abc'
I cannot work out how to fix that (other than to build up StrRev one
word at a time rather than doing replaces.
Damien|||yes what you said is correct:
heres a fix for that.. this should work try...
set nocount on
declare @.str Varchar(255)
declare @.id char(15)
declare @.strRev Varchar(255)
declare @.word Varchar(255)
declare @.newWord Varchar(255)
declare @.len int
declare @.pos int
Declare Cur_Rev Cursor
For select id, name from TestRev
open Cur_Rev
Fetch next from Cur_Rev into @.id, @.str
while @.@.fetch_status = 0
begin
set @.str = @.str + ' '
set @.strRev = ''
set @.len = LEN(@.str)
while (@.len > 1)
begin
set @.pos = PATINDEX('% %',@.str)
set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
if IsNumeric(@.word) <> 1
Begin
Select @.newword = reverse(@.word)
set @.strRev = @.strRev + ' ' + @.newWord
End
if IsNumeric(@.word) = 1
set @.strRev = @.strRev + ' ' + @.word
set @.str = substring(@.str, @.pos+1, @.len)
set @.len = len(@.str)
if @.pos = 0
break
end
select ltrim(rtrim(@.strRev)) 'Reversed'
fetch next from Cur_Rev into @.id, @.str
set @.strRev = @.str
end
Close Cur_Rev
Deallocate Cur_Rev
go
Thank,
Prad
"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
> Pradeep Kutty wrote:
> Doesn't work for if the string contains a string and it's reverse:
> insert into TestRev values (5,'abc cba abc cba')
> and run again (I get) 'abc abc abc abc'
> I cannot work out how to fix that (other than to build up StrRev one
> word at a time rather than doing replaces.
> Damien
>|||If you have a value seperator (delimiter) you could use this.
e.g
-- digits table needs to be created once
select top 8000 digit=identity(int,1,1)
into digits
from sysobjects,syscolumns
go
create function dbo.xreverse(@.input varchar(8000))
returns varchar(8000)
as
begin
declare @.tb table (i int identity primary key, value sysname)
declare @.s varchar(8000)
declare @.delim char
set @.delim=space(1)
set @.input = @.delim+rtrim(ltrim(@.input))+@.delim
insert @.tb
select case when isnumeric(value)=0 then reverse(value) else value end
from (
select substring(@.input, n.digit+1,
charindex(@.delim,@.input,n.digit+1)-n.digit-1) value
from digits as n
where n.digit<len(@.input)
and substring(@.input,n.digit,1) = @.delim
)x
select @.s=isnull(@.s+@.delim,'')+value from @.tb
return @.s
end
go
select dbo.xreverse(Name)
from TestRev
-oj
"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
> Pradeep Kutty wrote:
> Doesn't work for if the string contains a string and it's reverse:
> insert into TestRev values (5,'abc cba abc cba')
> and run again (I get) 'abc abc abc abc'
> I cannot work out how to fix that (other than to build up StrRev one
> word at a time rather than doing replaces.
> Damien
>|||in most cases it's work nice
but in cases like "cba (2000) fed" or "cba 2/3 fed" or "cba 2-3 fed"
it's wrong
sam
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:elyhOYdmFHA.3828@.TK2MSFTNGP12.phx.gbl...
> yes what you said is correct:
> heres a fix for that.. this should work try...
>
> set nocount on
> declare @.str Varchar(255)
> declare @.id char(15)
> declare @.strRev Varchar(255)
> declare @.word Varchar(255)
> declare @.newWord Varchar(255)
> declare @.len int
> declare @.pos int
>
> Declare Cur_Rev Cursor
> For select id, name from TestRev
>
> open Cur_Rev
> Fetch next from Cur_Rev into @.id, @.str
> while @.@.fetch_status = 0
> begin
> set @.str = @.str + ' '
> set @.strRev = ''
> set @.len = LEN(@.str)
> while (@.len > 1)
> begin
> set @.pos = PATINDEX('% %',@.str)
> set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
> if IsNumeric(@.word) <> 1
> Begin
> Select @.newword = reverse(@.word)
> set @.strRev = @.strRev + ' ' + @.newWord
> End
> if IsNumeric(@.word) = 1
> set @.strRev = @.strRev + ' ' + @.word
> set @.str = substring(@.str, @.pos+1, @.len)
> set @.len = len(@.str)
> if @.pos = 0
> break
> end
> select ltrim(rtrim(@.strRev)) 'Reversed'
> fetch next from Cur_Rev into @.id, @.str
> set @.strRev = @.str
> end
> Close Cur_Rev
> Deallocate Cur_Rev
> go
> Thank,
> Prad
> "Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
> news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
>

reverse merge repliction

hi

I have notice that i missed few columns in my merge repliction and i wonder is it possiblie to reverse a replicated database or copy to normal SQL database?

after that i would do a new Publication.

I need to have the data that is in the Database.

If this is SQL Server 2005, you can always use alter table and add the new columns to the table. They should automatically propagate to the subscriber.

If you wish you can drop the publication, add the columns and reset the publication too.

The data in the tables will be intact.

reverse merge repliction

hi

I have notice that i missed few columns in my merge repliction and i wonder is it possiblie to reverse a replicated database or copy to normal SQL database?

after that i would do a new Publication.

I need to have the data that is in the Database.

If this is SQL Server 2005, you can always use alter table and add the new columns to the table. They should automatically propagate to the subscriber.

If you wish you can drop the publication, add the columns and reset the publication too.

The data in the tables will be intact.

Reverse log shipping

We are in the planning stage of a redundant SQL environment and had a
question about log shipping.
We would like to double our use of the standby server and use it as a
pre-production server from time to time. Meaning, we would like to import
and test data on our standby server and then be able to log ship the changes
over to the primary server. Is this possible/recommended? If so, how would
it be done ?
Thanks
Nick
No.
Your better option is to move the DDL changes by script, or by using a 3rd
party tool such as Red Gate's SQL Compare.
You can move data using Script, DTS/SSIS, Red Gate's SQL Data Compare, etc.
You cannot change the data/schema in a Log Shipping Destination server
(whether its the Prod box or the standby) without Recovering it first, which
stops the Log Shipping part.
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"N." <larosan@.yahoo.com> wrote in message news:wR6wh.1$_i4.0@.newsfe09.lga...
> We are in the planning stage of a redundant SQL environment and had a
> question about log shipping.
> We would like to double our use of the standby server and use it as a
> pre-production server from time to time. Meaning, we would like to import
> and test data on our standby server and then be able to log ship the
> changes over to the primary server. Is this possible/recommended? If so,
> how would it be done ?
> Thanks
> Nick
>

reverse like?

I am trying to do the equivalent of following pseudo-code:
select id from tablename where %stringcolumn like 'mystring'
so I would have a record returned if the values in stringcolumn were:
ystring
string
tring
ring
etc..
To me this seems like some kind of backwards 'like' but I can't get it
to work.
Any ideas?
ChandyHi Chandy,
I once did that to implement a caller identitfication in our company.
Due to the fact that not every phone extension of the customer was know
by us I cutr the numbers one by one frome the right side on, so it was
something like:
Calling number is: 087776-37 (know Number is 087776-0) the attempts
are:
1: 0877763
2: 087776 (matches 087776 of 087776-0) --Hit
It cut these down one by one in a procedure till I reached a minimun
number which has to be matched. So (my 0.02 $), write a procedure which
does that in a loop.
HTH, Jens Suessmeyer.|||Hi Chandy,
Just trying to get my head round what you want.
At what part do you finish, do you mean also if it contains ing, ng and g?
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<chandy@.totalise.co.uk> wrote in message
news:1137758853.742762.291710@.o13g2000cwo.googlegroups.com...
>I am trying to do the equivalent of following pseudo-code:
> select id from tablename where %stringcolumn like 'mystring'
> so I would have a record returned if the values in stringcolumn were:
> ystring
> string
> tring
> ring
> etc..
> To me this seems like some kind of backwards 'like' but I can't get it
> to work.
> Any ideas?
> Chandy
>|||Hi Tony,
Yes, it would match against ing, ng and g, but not an empty sting or
null.
In reality the data would be domains and sub-domains, so I would be
testing
subdomain.domain.com
and wanting to match a row with
domain.com
but there could be many levels of sub-domain and different levels of
domains in the data so I wouldn't want to keep splitting the string and
re-testing if possible.
Chandy|||Hi Chandy,
This something like what you want....
declare @.seq table (
numb tinyint not null
)
insert @.seq values( 1 )
insert @.seq values( 2 )
insert @.seq values( 3 )
insert @.seq values( 4 )
insert @.seq values( 5 )
insert @.seq values( 6 )
insert @.seq values( 7 )
insert @.seq values( 8 )
insert @.seq values( 9 )
insert @.seq values( 10 )
declare @.source table (
searchtext varchar(500) not null
)
insert @.source values( 'this should tring be shown' )
insert @.source values( 'not this' )
select right( 'mystring', q.numb )
from @.seq q
select *
from @.source s
inner join @.seq q on s.searchtext like '% ' + right( 'mystring',
q.numb ) + ' %'
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<chandy@.totalise.co.uk> wrote in message
news:1137761909.961033.42150@.g43g2000cwa.googlegroups.com...
> Hi Tony,
> Yes, it would match against ing, ng and g, but not an empty sting or
> null.
> In reality the data would be domains and sub-domains, so I would be
> testing
> subdomain.domain.com
> and wanting to match a row with
> domain.com
> but there could be many levels of sub-domain and different levels of
> domains in the data so I wouldn't want to keep splitting the string and
> re-testing if possible.
> Chandy
>|||Chandy, does this work for you?
ALTER TABLE MyTable
ADD ReverseMyColumn AS REVERSE(MyColumn)
CREATE INDEX IX_MyTable_ReverseMyColumn ON MyTable(ReverseMyColumn)
SELECT MyColumn
FROM MyTable
WHERE ReverseMyTable LIKE REVERSE(@.search)+'%'
HTH,
Gert-Jan
chandy@.totalise.co.uk wrote:
> Hi Tony,
> Yes, it would match against ing, ng and g, but not an empty sting or
> null.
> In reality the data would be domains and sub-domains, so I would be
> testing
> subdomain.domain.com
> and wanting to match a row with
> domain.com
> but there could be many levels of sub-domain and different levels of
> domains in the data so I wouldn't want to keep splitting the string and
> re-testing if possible.
> Chandy|||drop table #t
drop table #n
go
create table #t(id int, token varchar(10))
--create table #n(n int)
insert into #t values(1, 'ing')
insert into #t values(2, 'ring')
insert into #t values(3, 'mystring!')
insert into #t values(4, 'mystring')
go
select 1 n
into #n
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
go
select n,token,substring('mystring', #n.n,100) from #t, #n
where substring('mystring', #n.n,100)=#t.token
n token
-- -- --
6 ing ing
5 ring ring
1 mystring mystring
(3 row(s) affected)|||Thanks to all for the suggestions. The REVERSE column looks the most
interesting, with a trigger to update/populate it I guess, but before I
saw that I simply did a loop over substrings of decreasings length
until I get a result then break the loop. Not as efficient but works
for me just now.
Chandy

Reverse full name

Hello all,
I have a SQL db that stores first & last names in one field (i.e. John Doe).
Does anybody know how to get reporting services to show the name as
lastname, firstname?
Thanks.you could create a temp table in the SQL, then use a function to get the
first part of the string, everything up to the space for example.
Then use another function to get the last part of the string to get the last
name. You can then combine them into a single field with the last name first.
"Jeff Bentivoglio" wrote:
> Hello all,
> I have a SQL db that stores first & last names in one field (i.e. John Doe).
> Does anybody know how to get reporting services to show the name as
> lastname, firstname?
> Thanks.
>
>

Reverse equivalent to TOP

Is there anyway I can return only the last row of a query, like TOP does for the top most items?

I would like to return something like this:

SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something

Any help would be greatly appreciated. Thank you!

gerardkcohen:

Is there anyway I can return only the last row of a query, like TOP does for the top most items?

I would like to return something like this:

SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something

Any help would be greatly appreciated. Thank you!

gerardkcohen --

Use a subquery. Get IDs in the order you want using an "order by desc". Get the IDs you want using a "TOP". Get the data you want using a "select * ... where in". And so on.

Here is some sample code.

use northwind
go
--get all the rows, in order, to see what we are working with...
select * from Shippers order by ShipperID asc
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
1           Speedy Express                           (503) 555-9831
2           United Package                           (503) 555-3199
3           Federal Shipping                         (503) 555-9931
(3 row(s) affected)
*/
--get the top 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID asc)
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
1           Speedy Express                           (503) 555-9831
2           United Package                           (503) 555-3199
(2 row(s) affected)
*/
--get the bottom 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID desc)
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
3           Federal Shipping                         (503) 555-9931
2           United Package                           (503) 555-3199
(2 row(s) affected)
*/
 

HTH.

Thank you.

-- Mark Kamoski

|||

You can still use TOP 1 to get the bottom 1 by adding ORDER BY Column_C DESC

Like:

SELECT TOP 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC

--edited

|||

Yes remember to changeBOTTOM -> top

SELECTtop 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC

Reverse enginnering in VISIO 2002 (problem with sp_primarykey)

I have problem if I want to do reverse engineering in VISIO 2002.
The database uses primary key created with sp_primarykey an foreign keys with sp_foreingnkey

In VISIO - Database - Options - Drivers I had set the DDL script generationas follows:
Preffered version - 6.0
Generate primary key using - sp_primarykey
Generate foregin key using - sp_foreignkey

The process had passed without errors, but the relations didn't showed.
I tried option "Show related tables" but nothing didn't happend.

thanks.That could possibily be because of the loss of dependensies. The happens when you drop and recreate few objects in the database, which does ot re-establish the dependensies.

Try recompiling the objects or establishing the foreign keys again.

Thanks.

Reverse engineering tables

Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
Simon
Simon
Did you mean that ans engineer is a word like 'engineer'?
This script has written by Vyas Kondreddi. See if it helps you.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:#dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Is there anyway to analyse the tables in my database and reverse engineer
> the SQL out of them?
> I really need to SQL quite badly but I can't see any easy way to get it.
> Thanks to anyone who can help
> Simon
>
|||Make use of the scripting functionality available in SQL Server Enterprise
Manager.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:%23dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
Simon

Reverse engineering tables

Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
SimonSimon
Did you mean that ans engineer is a word like 'engineer'?
This script has written by Vyas Kondreddi. See if it helps you.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:#dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Is there anyway to analyse the tables in my database and reverse engineer
> the SQL out of them?
> I really need to SQL quite badly but I can't see any easy way to get it.
> Thanks to anyone who can help
> Simon
>|||Make use of the scripting functionality available in SQL Server Enterprise
Manager.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:%23dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
Simon

Reverse engineering tables

Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
SimonSimon
Did you mean that ans engineer is a word like 'engineer'?
This script has written by Vyas Kondreddi. See if it helps you.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:#dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Is there anyway to analyse the tables in my database and reverse engineer
> the SQL out of them?
> I really need to SQL quite badly but I can't see any easy way to get it.
> Thanks to anyone who can help
> Simon
>|||Make use of the scripting functionality available in SQL Server Enterprise
Manager.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:%23dC6oGRLEHA.2660@.TK2MSFTNGP09.phx.gbl...
Hi all,
Is there anyway to analyse the tables in my database and reverse engineer
the SQL out of them?
I really need to SQL quite badly but I can't see any easy way to get it.
Thanks to anyone who can help
Simon

reverse engineering MS SQL server 2k5

Hi Alan
I can certainly import a database and foreign keys in SQL 2005, for instance
if I choose the northwind database and go through the wizard making sure that
FKs are imported and I choose the option to add the tables later to a
diagram. I can then sekect the customers tables from the tables and views box
and drag it onto the diagram. Then right clicking show related tables will
bring in the orders and customercustomerdemo tables with their foreign keys.
John
"Alain R." wrote:

> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>
I had that issue also, I installed SP3 for Visio 2003 and it started working
after that . Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi Alan
> I can certainly import a database and foreign keys in SQL 2005, for instance
> if I choose the northwind database and go through the wizard making sure that
> FKs are imported and I choose the option to add the tables later to a
> diagram. I can then sekect the customers tables from the tables and views box
> and drag it onto the diagram. Then right clicking show related tables will
> bring in the orders and customercustomerdemo tables with their foreign keys.
> John
> "Alain R." wrote:
|||Hi
I was using Microsoft Office Visio for Enterprise Architects (11.7218.8132)
SP2
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I had that issue also, I installed SP3 for Visio 2003 and it started working
> after that . Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Bell" wrote:

reverse engineering MS SQL server 2k5

Hi,
I got a DB with more than 100 tables.
i tried to get it structure by Visio and its reverse engineering method
but Visio does not draw "links" among tables representing PK and FK
connections.
So do you know any other software which could do the job ?
maybe a simple freeware is enough, it is just to have an overview on
this database and its objects.
thanks a lot,
A.Hi Alain
Visio should import these links (at least it did in SQL 2000!) Usually I
import the structure and then draw drag what is required onto a diagrams
rather than importing them directly onto diagram.
John
"Alain R." wrote:
> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||If you just want to have an overview on the database and its objects, would
the existing "Database Diagrams" feature in SQL Server 2005 be good enough
for you?
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Alain R." wrote:
> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||Hi Alan
I can certainly import a database and foreign keys in SQL 2005, for instance
if I choose the northwind database and go through the wizard making sure that
FKs are imported and I choose the option to add the tables later to a
diagram. I can then sekect the customers tables from the tables and views box
and drag it onto the diagram. Then right clicking show related tables will
bring in the orders and customercustomerdemo tables with their foreign keys.
John
"Alain R." wrote:
> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||I had that issue also, I installed SP3 for Visio 2003 and it started working
after that :). Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
> Hi Alan
> I can certainly import a database and foreign keys in SQL 2005, for instance
> if I choose the northwind database and go through the wizard making sure that
> FKs are imported and I choose the option to add the tables later to a
> diagram. I can then sekect the customers tables from the tables and views box
> and drag it onto the diagram. Then right clicking show related tables will
> bring in the orders and customercustomerdemo tables with their foreign keys.
> John
> "Alain R." wrote:
> > Hi,
> >
> > I got a DB with more than 100 tables.
> > i tried to get it structure by Visio and its reverse engineering method
> > but Visio does not draw "links" among tables representing PK and FK
> > connections.
> >
> > So do you know any other software which could do the job ?
> > maybe a simple freeware is enough, it is just to have an overview on
> > this database and its objects.
> >
> > thanks a lot,
> >
> > A.
> >|||Hi
I was using Microsoft Office Visio for Enterprise Architects (11.7218.8132)
SP2
John
"Mohit K. Gupta" wrote:
> I had that issue also, I installed SP3 for Visio 2003 and it started working
> after that :). Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Bell" wrote:
> > Hi Alan
> >
> > I can certainly import a database and foreign keys in SQL 2005, for instance
> > if I choose the northwind database and go through the wizard making sure that
> > FKs are imported and I choose the option to add the tables later to a
> > diagram. I can then sekect the customers tables from the tables and views box
> > and drag it onto the diagram. Then right clicking show related tables will
> > bring in the orders and customercustomerdemo tables with their foreign keys.
> >
> > John
> >
> > "Alain R." wrote:
> >
> > > Hi,
> > >
> > > I got a DB with more than 100 tables.
> > > i tried to get it structure by Visio and its reverse engineering method
> > > but Visio does not draw "links" among tables representing PK and FK
> > > connections.
> > >
> > > So do you know any other software which could do the job ?
> > > maybe a simple freeware is enough, it is just to have an overview on
> > > this database and its objects.
> > >
> > > thanks a lot,
> > >
> > > A.
> > >

reverse engineering MS SQL server 2k5

Hi,
I got a DB with more than 100 tables.
i tried to get it structure by visio and its reverse engineering method
but visio does not draw "links" among tables representing PK and FK
connections.
So do you know any other software which could do the job ?
maybe a simple freeware is enough, it is just to have an overview on
this database and its objects.
thanks a lot,
A.Hi Alain
Visio should import these links (at least it did in SQL 2000!) Usually I
import the structure and then draw drag what is required onto a diagrams
rather than importing them directly onto diagram.
John
"Alain R." wrote:

> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by visio and its reverse engineering method
> but visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||If you just want to have an overview on the database and its objects, would
the existing "Database Diagrams" feature in SQL Server 2005 be good enough
for you?
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Alain R." wrote:

> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by visio and its reverse engineering method
> but visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||Hi Alan
I can certainly import a database and foreign keys in SQL 2005, for instance
if I choose the northwind database and go through the wizard making sure tha
t
FKs are imported and I choose the option to add the tables later to a
diagram. I can then sekect the customers tables from the tables and views bo
x
and drag it onto the diagram. Then right clicking show related tables will
bring in the orders and customercustomerdemo tables with their foreign keys.
John
"Alain R." wrote:

> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by visio and its reverse engineering method
> but visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>|||I had that issue also, I installed SP3 for visio 2003 and it started working
after that . Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi Alan
> I can certainly import a database and foreign keys in SQL 2005, for instan
ce
> if I choose the northwind database and go through the wizard making sure t
hat
> FKs are imported and I choose the option to add the tables later to a
> diagram. I can then sekect the customers tables from the tables and views
box
> and drag it onto the diagram. Then right clicking show related tables will
> bring in the orders and customercustomerdemo tables with their foreign key
s.
> John
> "Alain R." wrote:
>|||Hi
I was using Microsoft Office visio for Enterprise Architects (11.7218.8132)
SP2
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I had that issue also, I installed SP3 for visio 2003 and it started worki
ng
> after that . Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Bell" wrote:
>

Reverse Engineering - FK

Hi there --
Can some one clue me in about Foriegn Keys and how I can determine what they
are.
I'm trying to reverse engineer a DB and I see some fields that have
interesting values. And these coumns do not appear to be in the table when I
select * from
The values I see are ...
=(1ABF96C75BA790
=G312296BA577411
$0000960D431B939
Do any these look like they are foriegn keys? And if so, how can I confirm?
Thanks
Mark,
Use sp_help tablename - where tablename is the table in question.
HTH
Jerry
"X-Mark" <X-Mark@.discussions.microsoft.com> wrote in message
news:2EFD8862-0E4B-42AB-882F-ECA69DC484E5@.microsoft.com...
> Hi there --
> Can some one clue me in about Foriegn Keys and how I can determine what
> they
> are.
> I'm trying to reverse engineer a DB and I see some fields that have
> interesting values. And these coumns do not appear to be in the table
> when I
> select * from
> The values I see are ...
> =(1ABF96C75BA790
> =G312296BA577411
> $0000960D431B939
> Do any these look like they are foriegn keys? And if so, how can I
> confirm?
> Thanks

Reverse Engineering - FK

Hi there --
Can some one clue me in about Foriegn Keys and how I can determine what they
are.
I'm trying to reverse engineer a DB and I see some fields that have
interesting values. And these coumns do not appear to be in the table when
I
select * from
The values I see are ...
=(1ABF96C75BA790
=G312296BA577411
$0000960D431B939
Do any these look like they are foriegn keys? And if so, how can I confirm?
ThanksMark,
Use sp_help tablename - where tablename is the table in question.
HTH
Jerry
"X-Mark" <X-Mark@.discussions.microsoft.com> wrote in message
news:2EFD8862-0E4B-42AB-882F-ECA69DC484E5@.microsoft.com...
> Hi there --
> Can some one clue me in about Foriegn Keys and how I can determine what
> they
> are.
> I'm trying to reverse engineer a DB and I see some fields that have
> interesting values. And these coumns do not appear to be in the table
> when I
> select * from
> The values I see are ...
> =(1ABF96C75BA790
> =G312296BA577411
> $0000960D431B939
> Do any these look like they are foriegn keys? And if so, how can I
> confirm?
> Thanks

Reverse Engineering - FK

Hi there --
Can some one clue me in about Foriegn Keys and how I can determine what they
are.
I'm trying to reverse engineer a DB and I see some fields that have
interesting values. And these coumns do not appear to be in the table when I
select * from
The values I see are ...
=(1ABF96C75BA790
=G312296BA577411
$0000960D431B939
Do any these look like they are foriegn keys? And if so, how can I confirm?
ThanksMark,
Use sp_help tablename - where tablename is the table in question.
HTH
Jerry
"X-Mark" <X-Mark@.discussions.microsoft.com> wrote in message
news:2EFD8862-0E4B-42AB-882F-ECA69DC484E5@.microsoft.com...
> Hi there --
> Can some one clue me in about Foriegn Keys and how I can determine what
> they
> are.
> I'm trying to reverse engineer a DB and I see some fields that have
> interesting values. And these coumns do not appear to be in the table
> when I
> select * from
> The values I see are ...
> =(1ABF96C75BA790
> =G312296BA577411
> $0000960D431B939
> Do any these look like they are foriegn keys? And if so, how can I
> confirm?
> Thanks