Search/Find Engine

Search/Find Engine

Post by Nathan Floy » Wed, 19 Jun 2002 00:00:28



Is there a way a user can search Enterprise Manager (MS SQL Server)  for
keywords on the database. TOAD had one for oracle and I am kind of looking
for the same thing.
 
 
 

Search/Find Engine

Post by MegaMa » Wed, 19 Jun 2002 01:03:02




Quote:> Is there a way a user can search Enterprise Manager (MS SQL Server)  for
> keywords on the database. TOAD had one for oracle and I am kind of looking
> for the same thing.

I' don't know oracle, but you can look on SQL Server BOL about    LIKE   and
SOUNDEX

Max

 
 
 

Search/Find Engine

Post by BP Margoli » Wed, 19 Jun 2002 01:42:45


Nathan,

It's unclear as to whether you are looking for keywords in the **data** or
in the object names (tables, views, etc.).

However, in either case, Enterprise Manager does not support a "search on
keyword" feature for either of these.

Assuming that you want to find keywords in the data, check out the
documentation on full-text indexing in the SQL Server Books Online.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Is there a way a user can search Enterprise Manager (MS SQL Server)  for
> keywords on the database. TOAD had one for oracle and I am kind of looking
> for the same thing.

 
 
 

Search/Find Engine

Post by Nathan Floy » Wed, 19 Jun 2002 02:13:48


I will and thanks....I am looking for a way to search the objects....for
example I need to write a query that pulls in address but I don't know where
address is held.  I can look through the Irwin diagram but it would be a lot
faster to do a search on keyword and get a listing of all of the tables that
have a column header of Address.  Now I am wondering if I can build a
utility that does that.  hmmm....if I have the time it might be fun.

Thanks again


> Nathan,

> It's unclear as to whether you are looking for keywords in the **data** or
> in the object names (tables, views, etc.).

> However, in either case, Enterprise Manager does not support a "search on
> keyword" feature for either of these.

> Assuming that you want to find keywords in the data, check out the
> documentation on full-text indexing in the SQL Server Books Online.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Is there a way a user can search Enterprise Manager (MS SQL Server)  for
> > keywords on the database. TOAD had one for oracle and I am kind of
looking
> > for the same thing.

 
 
 

Search/Find Engine

Post by Erland Sommarsko » Wed, 19 Jun 2002 07:50:56



> I will and thanks....I am looking for a way to search the objects....for
> example I need to write a query that pulls in address but I don't know
> where address is held.  I can look through the Irwin diagram but it
> would be a lot faster to do a search on keyword and get a listing of all
> of the tables that have a column header of Address.  Now I am wondering
> if I can build a utility that does that.  hmmm....if I have the time it
> might be fun.

   SELECT o.name
   FROM   syscolumns c
   JOIN   sysobjects o ON c.id = o.id
   WHERE  c.name = 'Address'
     AND  o.xtype = 'U'

will do what you want, but this is not a good way of approaching
your data model. There are not really any short-cuts. If you are
to program against a data model, you need to learn it. Of course,
you don't to learn every column by heart, but you need to have a
general feel for where the things you are looking for.

If you are using ErWin, then this is the best place to look.
Maybe not in the diagram, if the data model has many tables,
but ErWin should also offer various possibilities to produce
reports, possibly in HTML with nice links and that. (I use an-
other data-modelling tool myself, so I don't know exactly what
ErWin is capable of.)

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

Search/Find Engine

Post by BP Margoli » Wed, 19 Jun 2002 09:45:13


Nathan,

Erland prefers direct querying of the system tables, although Microsoft
recommends, when possible, avoiding direct querying of the system tables ...
at least in a production environment.

SQL Server supports the ANSI SQL standard INFORMATION_SCHEMA views which can
usually provide the same set of information as direct querying of the system
tables ... in fact, the INFORMATION_SCHEMA views are just views of the SQL
Server tables.

The equivalent INFORMATION_SCHEMA query to the one posted by Erland is:

SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS c
  ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA  AND
        t.TABLE_NAME   = c.TABLE_NAME)
WHERE t.TABLE_TYPE  = 'BASE TABLE'
AND   c.COLUMN_NAME = 'Address'

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> I will and thanks....I am looking for a way to search the objects....for
> example I need to write a query that pulls in address but I don't know
where
> address is held.  I can look through the Irwin diagram but it would be a
lot
> faster to do a search on keyword and get a listing of all of the tables
that
> have a column header of Address.  Now I am wondering if I can build a
> utility that does that.  hmmm....if I have the time it might be fun.

> Thanks again



> > Nathan,

> > It's unclear as to whether you are looking for keywords in the **data**
or
> > in the object names (tables, views, etc.).

> > However, in either case, Enterprise Manager does not support a "search
on
> > keyword" feature for either of these.

> > Assuming that you want to find keywords in the data, check out the
> > documentation on full-text indexing in the SQL Server Books Online.

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > Is there a way a user can search Enterprise Manager (MS SQL Server)
for
> > > keywords on the database. TOAD had one for oracle and I am kind of
> looking
> > > for the same thing.

 
 
 

Search/Find Engine

Post by D » Fri, 21 Jun 2002 05:28:03


Hi BP,

"although Microsoft recommends, when possible, avoiding direct
querying of the system tables ... at least in a production
environment."
Could you kindly point out where the above statement come from?  BOL?
Thanks.

Don


> Nathan,

> Erland prefers direct querying of the system tables, although Microsoft
> recommends, when possible, avoiding direct querying of the system tables ...
> at least in a production environment.

> SQL Server supports the ANSI SQL standard INFORMATION_SCHEMA views which can
> usually provide the same set of information as direct querying of the system
> tables ... in fact, the INFORMATION_SCHEMA views are just views of the SQL
> Server tables.

> The equivalent INFORMATION_SCHEMA query to the one posted by Erland is:

> SELECT t.TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES AS t
> JOIN INFORMATION_SCHEMA.COLUMNS AS c
>   ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA  AND
>         t.TABLE_NAME   = c.TABLE_NAME)
> WHERE t.TABLE_TYPE  = 'BASE TABLE'
> AND   c.COLUMN_NAME = 'Address'

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I will and thanks....I am looking for a way to search the objects....for
> > example I need to write a query that pulls in address but I don't know
>  where
> > address is held.  I can look through the Irwin diagram but it would be a
>  lot
> > faster to do a search on keyword and get a listing of all of the tables
>  that
> > have a column header of Address.  Now I am wondering if I can build a
> > utility that does that.  hmmm....if I have the time it might be fun.

> > Thanks again



> > > Nathan,

> > > It's unclear as to whether you are looking for keywords in the **data**
>  or
> > > in the object names (tables, views, etc.).

> > > However, in either case, Enterprise Manager does not support a "search
>  on
> > > keyword" feature for either of these.

> > > Assuming that you want to find keywords in the data, check out the
> > > documentation on full-text indexing in the SQL Server Books Online.

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
>  which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > Is there a way a user can search Enterprise Manager (MS SQL Server)
>  for
> > > > keywords on the database. TOAD had one for oracle and I am kind of
>  looking
> > > > for the same thing.

 
 
 

Search/Find Engine

Post by Luc » Fri, 21 Jun 2002 06:10:08


It's probably somewhere in BOL but believe me that's absolutely correct. The
reason is that when new functionality is added that affects metadata
(-metadata), a change to the system tables is unavoidable. So this is for
backwards compatilibity reasons.

Luc


> Hi BP,

> "although Microsoft recommends, when possible, avoiding direct
> querying of the system tables ... at least in a production
> environment."
> Could you kindly point out where the above statement come from?  BOL?
> Thanks.

> Don




> > Nathan,

> > Erland prefers direct querying of the system tables, although Microsoft
> > recommends, when possible, avoiding direct querying of the system tables
...
> > at least in a production environment.

> > SQL Server supports the ANSI SQL standard INFORMATION_SCHEMA views which
can
> > usually provide the same set of information as direct querying of the
system
> > tables ... in fact, the INFORMATION_SCHEMA views are just views of the
SQL
> > Server tables.

> > The equivalent INFORMATION_SCHEMA query to the one posted by Erland is:

> > SELECT t.TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES AS t
> > JOIN INFORMATION_SCHEMA.COLUMNS AS c
> >   ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA  AND
> >         t.TABLE_NAME   = c.TABLE_NAME)
> > WHERE t.TABLE_TYPE  = 'BASE TABLE'
> > AND   c.COLUMN_NAME = 'Address'

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > I will and thanks....I am looking for a way to search the
objects....for
> > > example I need to write a query that pulls in address but I don't know
> >  where
> > > address is held.  I can look through the Irwin diagram but it would be
a
> >  lot
> > > faster to do a search on keyword and get a listing of all of the
tables
> >  that
> > > have a column header of Address.  Now I am wondering if I can build a
> > > utility that does that.  hmmm....if I have the time it might be fun.

> > > Thanks again



> > > > Nathan,

> > > > It's unclear as to whether you are looking for keywords in the
**data**
> >  or
> > > > in the object names (tables, views, etc.).

> > > > However, in either case, Enterprise Manager does not support a
"search
> >  on
> > > > keyword" feature for either of these.

> > > > Assuming that you want to find keywords in the data, check out the
> > > > documentation on full-text indexing in the SQL Server Books Online.

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> >  which
> > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > Is there a way a user can search Enterprise Manager (MS SQL
Server)
> >  for
> > > > > keywords on the database. TOAD had one for oracle and I am kind of
> >  looking
> > > > > for the same thing.

 
 
 

Search/Find Engine

Post by BP Margoli » Fri, 21 Jun 2002 06:31:40


Don,

1. The SQL Server 7.0 BOL explicitly stated this. The SQL Server 2000 BOL
has relaxed this "restriction" implying that Microsoft will always change
the system tables in such a way as to support backwards compatibility, but,
frankly, that hasn't happened ... there are some discrepancies between the
behavior of the system stored procedures in SS7 and SS2K even though one of
the prime reasons for using system stored procedures is Microsoft's
guarantee that they will not "break" the functionality of the system stored
procedures in future releases of SQL Server.

2. I have no doubt that Microsoft strives to implement changes to support
backwards compatibility, but that just ain't always possible, and Microsoft,
when push comes to shove, will choose greater functionality rather than
backwards compatibility (and I agree that they should).

3. MS Product Support Personnel are themselves somewhat divided on this
topic ... some post solutions directly referencing the system tables, while
others post warnings against directly referencing system tables.

4. Do you really want to be left holding the bag when Microsoft says one
thing and does something else. The ANSI INFORMATION_SCHEMA views are not
"owned" by Microsoft, so Microsoft can't arbitrarily change them. The SQL
Server system tables are owned by Microsoft, and they will change them.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Hi BP,

> "although Microsoft recommends, when possible, avoiding direct
> querying of the system tables ... at least in a production
> environment."
> Could you kindly point out where the above statement come from?  BOL?
> Thanks.

> Don




> > Nathan,

> > Erland prefers direct querying of the system tables, although Microsoft
> > recommends, when possible, avoiding direct querying of the system tables
...
> > at least in a production environment.

> > SQL Server supports the ANSI SQL standard INFORMATION_SCHEMA views which
can
> > usually provide the same set of information as direct querying of the
system
> > tables ... in fact, the INFORMATION_SCHEMA views are just views of the
SQL
> > Server tables.

> > The equivalent INFORMATION_SCHEMA query to the one posted by Erland is:

> > SELECT t.TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES AS t
> > JOIN INFORMATION_SCHEMA.COLUMNS AS c
> >   ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA  AND
> >         t.TABLE_NAME   = c.TABLE_NAME)
> > WHERE t.TABLE_TYPE  = 'BASE TABLE'
> > AND   c.COLUMN_NAME = 'Address'

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > I will and thanks....I am looking for a way to search the
objects....for
> > > example I need to write a query that pulls in address but I don't know
> >  where
> > > address is held.  I can look through the Irwin diagram but it would be
a
> >  lot
> > > faster to do a search on keyword and get a listing of all of the
tables
> >  that
> > > have a column header of Address.  Now I am wondering if I can build a
> > > utility that does that.  hmmm....if I have the time it might be fun.

> > > Thanks again



> > > > Nathan,

> > > > It's unclear as to whether you are looking for keywords in the
**data**
> >  or
> > > > in the object names (tables, views, etc.).

> > > > However, in either case, Enterprise Manager does not support a
"search
> >  on
> > > > keyword" feature for either of these.

> > > > Assuming that you want to find keywords in the data, check out the
> > > > documentation on full-text indexing in the SQL Server Books Online.

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> >  which
> > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > Is there a way a user can search Enterprise Manager (MS SQL
Server)
> >  for
> > > > > keywords on the database. TOAD had one for oracle and I am kind of
> >  looking
> > > > > for the same thing.

 
 
 

Search/Find Engine

Post by D » Sat, 22 Jun 2002 12:38:39


BP,

Thank you very much for the perfect explanation.  Too bad in the real
world, however, we don't have a perfect solution to many problems :)

Initially, I had this thinking that INFORMATION_SCHEMA views are
mostly drawn from system tables, which may join more tables to get
something for a view, on the other hand, if I go directly to certain
system table to get something for what I need, it might be a short
cut.  It seems I may be wrong.  Is there any way that I can display
the script of an INFORMATION_SCHEMA view?  Like sp_helptext someSP
Well, hold on for a second, I just checked BOL on this, it says
"INFORMATION_SCHEMA.TABLES view is based on the sysobjects system
table."  So, I may not be wrong.  But if Microsoft's interpretation of
system table, sysobjects for instance, differs from one instance to
another, it creates discrepancy, and indicates that it's not prudent
to use system table for metadata if we are in an environment with
multiple versions of MS SQL Server.

Again, I appreciate it, you're the best.

Don


> Don,

> 1. The SQL Server 7.0 BOL explicitly stated this. The SQL Server 2000 BOL
> has relaxed this "restriction" implying that Microsoft will always change
> the system tables in such a way as to support backwards compatibility, but,
> frankly, that hasn't happened ... there are some discrepancies between the
> behavior of the system stored procedures in SS7 and SS2K even though one of
> the prime reasons for using system stored procedures is Microsoft's
> guarantee that they will not "break" the functionality of the system stored
> procedures in future releases of SQL Server.

> 2. I have no doubt that Microsoft strives to implement changes to support
> backwards compatibility, but that just ain't always possible, and Microsoft,
> when push comes to shove, will choose greater functionality rather than
> backwards compatibility (and I agree that they should).

> 3. MS Product Support Personnel are themselves somewhat divided on this
> topic ... some post solutions directly referencing the system tables, while
> others post warnings against directly referencing system tables.

> 4. Do you really want to be left holding the bag when Microsoft says one
> thing and does something else. The ANSI INFORMATION_SCHEMA views are not
> "owned" by Microsoft, so Microsoft can't arbitrarily change them. The SQL
> Server system tables are owned by Microsoft, and they will change them.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Hi BP,

> > "although Microsoft recommends, when possible, avoiding direct
> > querying of the system tables ... at least in a production
> > environment."
> > Could you kindly point out where the above statement come from?  BOL?
> > Thanks.

> > Don



> > > Nathan,

> > > Erland prefers direct querying of the system tables, although Microsoft
> > > recommends, when possible, avoiding direct querying of the system tables
>  ...
> > > at least in a production environment.

> > > SQL Server supports the ANSI SQL standard INFORMATION_SCHEMA views which
>  can
> > > usually provide the same set of information as direct querying of the
>  system
> > > tables ... in fact, the INFORMATION_SCHEMA views are just views of the
>  SQL
> > > Server tables.

> > > The equivalent INFORMATION_SCHEMA query to the one posted by Erland is:

> > > SELECT t.TABLE_NAME
> > > FROM INFORMATION_SCHEMA.TABLES AS t
> > > JOIN INFORMATION_SCHEMA.COLUMNS AS c
> > >   ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA  AND
> > >         t.TABLE_NAME   = c.TABLE_NAME)
> > > WHERE t.TABLE_TYPE  = 'BASE TABLE'
> > > AND   c.COLUMN_NAME = 'Address'

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
>  which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > I will and thanks....I am looking for a way to search the
>  objects....for
> > > > example I need to write a query that pulls in address but I don't know
>  where
> > > > address is held.  I can look through the Irwin diagram but it would be
>  a
>  lot
> > > > faster to do a search on keyword and get a listing of all of the
>  tables
>  that
> > > > have a column header of Address.  Now I am wondering if I can build a
> > > > utility that does that.  hmmm....if I have the time it might be fun.

> > > > Thanks again



> > > > > Nathan,

> > > > > It's unclear as to whether you are looking for keywords in the
>  **data**
>  or
> > > > > in the object names (tables, views, etc.).

> > > > > However, in either case, Enterprise Manager does not support a
>  "search
>  on
> > > > > keyword" feature for either of these.

> > > > > Assuming that you want to find keywords in the data, check out the
> > > > > documentation on full-text indexing in the SQL Server Books Online.

> > > > > -------------------------------------------
> > > > > BP Margolin
> > > > > Please reply only to the newsgroups.
> > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
>  which
> > > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > > Is there a way a user can search Enterprise Manager (MS SQL
>  Server)
>  for
> > > > > > keywords on the database. TOAD had one for oracle and I am kind of
>  looking
> > > > > > for the same thing.

 
 
 

Search/Find Engine

Post by Steve Kas » Sat, 22 Jun 2002 13:44:19


Don,

  The view definitions can be found in ansiview.sql, in the install directory
of your sql server directory.  For example, this is the tables view, which is
indeed based on sysobjects in my version of SQL Server (2000 developer, sp2).

create view INFORMATION_SCHEMA.TABLES
as
select  distinct
 db_name()   as TABLE_CATALOG
 ,user_name(o.uid) as TABLE_SCHEMA
 ,o.name    as TABLE_NAME
 ,case o.xtype
  when 'U' then 'BASE TABLE'
  when 'V' then 'VIEW'
 end     as TABLE_TYPE
from
 sysobjects o
where
 o.xtype in ('U', 'V') and
 permissions(o.id) != 0
go

Steve Kass
Drew University


> BP,

> Thank you very much for the perfect explanation.  Too bad in the real
> world, however, we don't have a perfect solution to many problems :)

> Initially, I had this thinking that INFORMATION_SCHEMA views are
> mostly drawn from system tables, which may join more tables to get
> something for a view, on the other hand, if I go directly to certain
> system table to get something for what I need, it might be a short
> cut.  It seems I may be wrong.  Is there any way that I can display
> the script of an INFORMATION_SCHEMA view?  Like sp_helptext someSP
> Well, hold on for a second, I just checked BOL on this, it says
> "INFORMATION_SCHEMA.TABLES view is based on the sysobjects system
> table."  So, I may not be wrong.  But if Microsoft's interpretation of
> system table, sysobjects for instance, differs from one instance to
> another, it creates discrepancy, and indicates that it's not prudent
> to use system table for metadata if we are in an environment with
> multiple versions of MS SQL Server.

> Again, I appreciate it, you're the best.

> Don


> > Don,

> > 1. The SQL Server 7.0 BOL explicitly stated this. The SQL Server 2000 BOL
> > has relaxed this "restriction" implying that Microsoft will always change
> > the system tables in such a way as to support backwards compatibility, but,
> > frankly, that hasn't happened ... there are some discrepancies between the
> > behavior of the system stored procedures in SS7 and SS2K even though one of
> > the prime reasons for using system stored procedures is Microsoft's
> > guarantee that they will not "break" the functionality of the system stored
> > procedures in future releases of SQL Server.

> > 2. I have no doubt that Microsoft strives to implement changes to support
> > backwards compatibility, but that just ain't always possible, and Microsoft,
> > when push comes to shove, will choose greater functionality rather than
> > backwards compatibility (and I agree that they should).

> > 3. MS Product Support Personnel are themselves somewhat divided on this
> > topic ... some post solutions directly referencing the system tables, while
> > others post warnings against directly referencing system tables.

> > 4. Do you really want to be left holding the bag when Microsoft says one
> > thing and does something else. The ANSI INFORMATION_SCHEMA views are not
> > "owned" by Microsoft, so Microsoft can't arbitrarily change them. The SQL
> > Server system tables are owned by Microsoft, and they will change them.

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > Hi BP,

> > > "although Microsoft recommends, when possible, avoiding direct
> > > querying of the system tables ... at least in a production
> > > environment."
> > > Could you kindly point out where the above statement come from?  BOL?
> > > Thanks.

> > > Don



> > > > Nathan,

> > > > Erland prefers direct querying of the system tables, although Microsoft
> > > > recommends, when possible, avoiding direct querying of the system tables
> >  ...
> > > > at least in a production environment.

> > > > SQL Server supports the ANSI SQL standard INFORMATION_SCHEMA views which
> >  can
> > > > usually provide the same set of information as direct querying of the
> >  system
> > > > tables ... in fact, the INFORMATION_SCHEMA views are just views of the
> >  SQL
> > > > Server tables.

> > > > The equivalent INFORMATION_SCHEMA query to the one posted by Erland is:

> > > > SELECT t.TABLE_NAME
> > > > FROM INFORMATION_SCHEMA.TABLES AS t
> > > > JOIN INFORMATION_SCHEMA.COLUMNS AS c
> > > >   ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA  AND
> > > >         t.TABLE_NAME   = c.TABLE_NAME)
> > > > WHERE t.TABLE_TYPE  = 'BASE TABLE'
> > > > AND   c.COLUMN_NAME = 'Address'

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> >  which
> > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > I will and thanks....I am looking for a way to search the
> >  objects....for
> > > > > example I need to write a query that pulls in address but I don't know
> >  where
> > > > > address is held.  I can look through the Irwin diagram but it would be
> >  a
> >  lot
> > > > > faster to do a search on keyword and get a listing of all of the
> >  tables
> >  that
> > > > > have a column header of Address.  Now I am wondering if I can build a
> > > > > utility that does that.  hmmm....if I have the time it might be fun.

> > > > > Thanks again



> > > > > > Nathan,

> > > > > > It's unclear as to whether you are looking for keywords in the
> >  **data**
> >  or
> > > > > > in the object names (tables, views, etc.).

> > > > > > However, in either case, Enterprise Manager does not support a
> >  "search
> >  on
> > > > > > keyword" feature for either of these.

> > > > > > Assuming that you want to find keywords in the data, check out the
> > > > > > documentation on full-text indexing in the SQL Server Books Online.

> > > > > > -------------------------------------------
> > > > > > BP Margolin
> > > > > > Please reply only to the newsgroups.
> > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> >  which
> > > > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > > > Is there a way a user can search Enterprise Manager (MS SQL
> >  Server)
> >  for
> > > > > > > keywords on the database. TOAD had one for oracle and I am kind of
> >  looking
> > > > > > > for the same thing.

 
 
 

Search/Find Engine

Post by oj » Sat, 22 Jun 2002 14:32:56


i see you've been diggin'...

--
-oj


Quote:> Don,

>   The view definitions can be found in ansiview.sql, in the install
directory
> of your sql server directory.  For example, this is the tables view, which
is
> indeed based on sysobjects in my version of SQL Server (2000 developer,
sp2).

> create view INFORMATION_SCHEMA.TABLES
> as
> select  distinct
>  db_name()   as TABLE_CATALOG
>  ,user_name(o.uid) as TABLE_SCHEMA
>  ,o.name    as TABLE_NAME
>  ,case o.xtype
>   when 'U' then 'BASE TABLE'
>   when 'V' then 'VIEW'
>  end     as TABLE_TYPE
> from
>  sysobjects o
> where
>  o.xtype in ('U', 'V') and
>  permissions(o.id) != 0
> go

> Steve Kass
> Drew University

 
 
 

Search/Find Engine

Post by MegaMa » Sat, 22 Jun 2002 15:39:21


Quote:>Is there any way that I can display
> the script of an INFORMATION_SCHEMA view?

They are VIEW of the "master" database
INFORMATION_SCHEMA.TABLES is the view TABLES and so on...

Max

 
 
 

Search/Find Engine

Post by Steve Kas » Sat, 22 Jun 2002 19:46:37


Yeah, there's lots of interesting stuff in the install queries.

Steve


> i see you've been diggin'...

> --
> -oj



> > Don,

> >   The view definitions can be found in ansiview.sql, in the install
> directory
> > of your sql server directory.  For example, this is the tables view, which
> is
> > indeed based on sysobjects in my version of SQL Server (2000 developer,
> sp2).

> > create view INFORMATION_SCHEMA.TABLES
> > as
> > select  distinct
> >  db_name()   as TABLE_CATALOG
> >  ,user_name(o.uid) as TABLE_SCHEMA
> >  ,o.name    as TABLE_NAME
> >  ,case o.xtype
> >   when 'U' then 'BASE TABLE'
> >   when 'V' then 'VIEW'
> >  end     as TABLE_TYPE
> > from
> >  sysobjects o
> > where
> >  o.xtype in ('U', 'V') and
> >  permissions(o.id) != 0
> > go

> > Steve Kass
> > Drew University

 
 
 

Search/Find Engine

Post by D » Sun, 23 Jun 2002 04:53:01


Steve,

Thank you very much.  It's real good to know.
Thanks, Luc and Max, for your follow-up as well.

oj, yes, I'm digging, just don't push me (over the edge :)

Regards,

Don

Steve Kass <sk...@drew.edu> wrote in message <news:3D12AF23.ABC1B614@drew.edu>...
> Don,

>   The view definitions can be found in ansiview.sql, in the install directory
> of your sql server directory.  For example, this is the tables view, which is
> indeed based on sysobjects in my version of SQL Server (2000 developer, sp2).

> create view INFORMATION_SCHEMA.TABLES
> as
> select  distinct
>  db_name()   as TABLE_CATALOG
>  ,user_name(o.uid) as TABLE_SCHEMA
>  ,o.name    as TABLE_NAME
>  ,case o.xtype
>   when 'U' then 'BASE TABLE'
>   when 'V' then 'VIEW'
>  end     as TABLE_TYPE
> from
>  sysobjects o
> where
>  o.xtype in ('U', 'V') and
>  permissions(o.id) != 0
> go

> Steve Kass
> Drew University

> DL wrote:

> > BP,

> > Thank you very much for the perfect explanation.  Too bad in the real
> > world, however, we don't have a perfect solution to many problems :)

> > Initially, I had this thinking that INFORMATION_SCHEMA views are
> > mostly drawn from system tables, which may join more tables to get
> > something for a view, on the other hand, if I go directly to certain
> > system table to get something for what I need, it might be a short
> > cut.  It seems I may be wrong.  Is there any way that I can display
> > the script of an INFORMATION_SCHEMA view?  Like sp_helptext someSP
> > Well, hold on for a second, I just checked BOL on this, it says
> > "INFORMATION_SCHEMA.TABLES view is based on the sysobjects system
> > table."  So, I may not be wrong.  But if Microsoft's interpretation of
> > system table, sysobjects for instance, differs from one instance to
> > another, it creates discrepancy, and indicates that it's not prudent
> > to use system table for metadata if we are in an environment with
> > multiple versions of MS SQL Server.

> > Again, I appreciate it, you're the best.

> > Don

> > "BP Margolin" <bpma...@attglobal.net> wrote in message <news:3d10fb8c_3@news1.prserv.net>...
> > > Don,

> > > 1. The SQL Server 7.0 BOL explicitly stated this. The SQL Server 2000 BOL
> > > has relaxed this "restriction" implying that Microsoft will always change
> > > the system tables in such a way as to support backwards compatibility, but,
> > > frankly, that hasn't happened ... there are some discrepancies between the
> > > behavior of the system stored procedures in SS7 and SS2K even though one of
> > > the prime reasons for using system stored procedures is Microsoft's
> > > guarantee that they will not "break" the functionality of the system stored
> > > procedures in future releases of SQL Server.

> > > 2. I have no doubt that Microsoft strives to implement changes to support
> > > backwards compatibility, but that just ain't always possible, and Microsoft,
> > > when push comes to shove, will choose greater functionality rather than
> > > backwards compatibility (and I agree that they should).

> > > 3. MS Product Support Personnel are themselves somewhat divided on this
> > > topic ... some post solutions directly referencing the system tables, while
> > > others post warnings against directly referencing system tables.

> > > 4. Do you really want to be left holding the bag when Microsoft says one
> > > thing and does something else. The ANSI INFORMATION_SCHEMA views are not
> > > "owned" by Microsoft, so Microsoft can't arbitrarily change them. The SQL
> > > Server system tables are owned by Microsoft, and they will change them.

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> > > can be cut and pasted into Query Analyzer is appreciated.

> > > "DL" <don...@yahoo.com> wrote in message
> > > news:604bb90c.0206191228.140615f4@posting.google.com...
> > > > Hi BP,

> > > > "although Microsoft recommends, when possible, avoiding direct
> > > > querying of the system tables ... at least in a production
> > > > environment."
> > > > Could you kindly point out where the above statement come from?  BOL?
> > > > Thanks.

> > > > Don

> > > > "BP Margolin" <bpma...@attglobal.net> wrote in message
>  <news:3d0e85e7_4@news1.prserv.net>...
> > > > > Nathan,

> > > > > Erland prefers direct querying of the system tables, although Microsoft
> > > > > recommends, when possible, avoiding direct querying of the system tables
>  ...
> > > > > at least in a production environment.

> > > > > SQL Server supports the ANSI SQL standard INFORMATION_SCHEMA views which
>  can
> > > > > usually provide the same set of information as direct querying of the
>  system
> > > > > tables ... in fact, the INFORMATION_SCHEMA views are just views of the
>  SQL
> > > > > Server tables.

> > > > > The equivalent INFORMATION_SCHEMA query to the one posted by Erland is:

> > > > > SELECT t.TABLE_NAME
> > > > > FROM INFORMATION_SCHEMA.TABLES AS t
> > > > > JOIN INFORMATION_SCHEMA.COLUMNS AS c
> > > > >   ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA  AND
> > > > >         t.TABLE_NAME   = c.TABLE_NAME)
> > > > > WHERE t.TABLE_TYPE  = 'BASE TABLE'
> > > > > AND   c.COLUMN_NAME = 'Address'

> > > > > -------------------------------------------
> > > > > BP Margolin
> > > > > Please reply only to the newsgroups.
> > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
>  which
> > > > > can be cut and pasted into Query Analyzer is appreciated.

> > > > > "Nathan Floyd" <n_t_fl...@hotmail.com> wrote in message
> > > > > news:ael26g$2s2$1@ih292.ea.unisys.com...
> > > > > > I will and thanks....I am looking for a way to search the
>  objects....for
> > > > > > example I need to write a query that pulls in address but I don't know
>  where
> > > > > > address is held.  I can look through the Irwin diagram but it would be
> > >  a
> > >  lot
> > > > > > faster to do a search on keyword and get a listing of all of the
> > >  tables
> > >  that
> > > > > > have a column header of Address.  Now I am wondering if I can build a
> > > > > > utility that does that.  hmmm....if I have the time it might be fun.

> > > > > > Thanks again

> > > > > > "BP Margolin" <bpma...@attglobal.net> wrote in message
> > > > > > news:3d0e14d2_1@news1.prserv.net...
> > > > > > > Nathan,

> > > > > > > It's unclear as to whether you are looking for keywords in the
> > >  **data**
> > >  or
> > > > > > > in the object names (tables, views, etc.).

> > > > > > > However, in either case, Enterprise Manager does not support a
> > >  "search
> > >  on
> > > > > > > keyword" feature for either of these.

> > > > > > > Assuming that you want to find keywords in the data, check out the
> > > > > > > documentation on full-text indexing in the SQL Server Books Online.

> > > > > > > -------------------------------------------
> > > > > > > BP Margolin
> > > > > > > Please reply only to the newsgroups.
> > > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
>  which
> > > > > > > can be cut and pasted into Query Analyzer is appreciated.

> > > > > > > "Nathan Floyd" <n_t_fl...@hotmail.com> wrote in message
> > > > > > > news:aekqcg$pas$1@ih292.ea.unisys.com...
> > > > > > > > Is there a way a user can search Enterprise Manager (MS SQL
> > >  Server)
> > >  for
> > > > > > > > keywords on the database. TOAD had one for oracle and I am kind of
>  looking
> > > > > > > > for the same thing.