INFORMATION_SCHEMA Views and Indexed Views

Hi,
I wanted to write some stored procedures to help me manage my indexed views
in SQL2000/2008. Since I wanted to follow the advice to use the
INFORMATION_SCHEMA views instead of system tables/catalog views. I have
this query that will run in both 2000 and 2008 and it correctly finds my
indexed views:
select *
from sysobjects
where type = 'V'
and id in (select id from sysindexes);
However, if I run this query in either 2000 or 2008 , it returns nothing:
select * from INFORMATION_SCHEMA.VIEWS
where TABLE_NAME in (
select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
)
The SQL 2008 BOL states "Returns one row for each column that is constrained
as a key in the current database.", and SQL 2000 BOL state "Contains one row
for each column, in the current database, that is constrained as a key."
Does anyone have any sage advice on this topic, or should I post it as a
Connect issue for SQL 2008?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>
The INFORMATION_SCHEMA describes only the logical features of the database:
tables, columns, constraints. Not indexes because they are a physical
implementation construct and aren't part of standard SQL like the
INFORMATION_SCHEMA.
For index information you need sys.indexes and sys.index_columns, or
dbo.sysindexes and dbo.sysindexkeys. That's unless the index is one that
supports a constraint, in which case the same information will be in
INFORMATION_SCHEMA.
--
David Portas|||Someone will point out what's going on and for every issue you encounter
they'll tell you how you screwed up (though usually politely - unless you
get celko). I'll probably get blasted for this post. However, personally, I
think INFORMATION_SCHEMA sucks and that the SQL Server catalog tables are
far, far superior and thought out much better (esp. 2005).
While I'll blame Microsoft for the poor docs on it, I don't blame them for
the bulk of my issues with it as they just implimented the ANSI standard. I
just don't like it and don't think it was well thought out.
Jay
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>

Information_schema and Procedures and logins

Is there an information_schema view to check if a proc / login exist?What is a proc / login?
For stored procedures, you can use information_schema.routines
AMB
"Chedva" wrote:

> Is there an information_schema view to check if a proc / login exist?
>
>|||Not for logins. For stored procedures, check out ROUTINES.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:%23XbQa9mRFHA.244@.TK2MSFTNGP12.phx
.gbl...
> Is there an information_schema view to check if a proc / login exist?
>|||Procedures are in SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE
ROUTINE_TYPE='PROCEDURE'
Logins are not held in information_schema views, you will have to go to
master.dbo.syslogins for that.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:%23XbQa9mRFHA.244@.TK2MSFTNGP12.phx.gbl...
> Is there an information_schema view to check if a proc / login exist?
>|||You can find stored procedures (and user defined functions) in
information_schema.routines. There are views for security
(INFORMATION_SCHEMA.COLUMN_PRIVILEGES and
INFORMATION_SCHEMA.TABLE_PRIVILEGES) but there is no view that lists all the
users in a database or the logins on a server. You will have to use
master..syslogins for that. Or you can use the stored procedure
sp_MShasdbaccess.
Jacco Schalkwijk
SQL Server MVP
"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:%23XbQa9mRFHA.244@.TK2MSFTNGP12.phx.gbl...
> Is there an information_schema view to check if a proc / login exist?
>

info..._schema.routines no Triggers listed - how to find scripts?

Hello,
when I look through Informatin_Schema.Routines I can find listings and
scripts for stored procedures and user-defined functions. Besides the
Trigger Manager, where can I find a collective listing of all Triggers in a
DB and the corresponding scripts? What I really want to do is to find a
Trigger that is using a particular UDF. In pseudo code I was hoping to do
something like this:
Select Routine_Name From Information_Schema.TriggerRoutines Where
Routine_Definition Like %dbo.f_someUDF%
Any suggestions appreciated how I can accomplish this.
Thanks,
Rich>What I really want to do is to find a
> Trigger that is using a particular UDF. In pseudo code I was hoping to do
> something like this:
If that's all that you really want to do, assuming that the trigger and the
function are in the same database
SQL 2000:
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) = 'YourFunction'
SQL 2005
SELECT DISTINCT OBJECT_NAME([id]) FROM sys.sql_dependencies
WHERE OBJECT_NAME([referenced_major_id]) = 'YourFunction'
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.

Indexes: changing/adding with Publication enabled

Are there any stored procedures available to alter indexes while Publication
is enabled?
What is sp_addscriptexec used for?
thank you,
bob
Dropping and creating indexes on the publisher is permited but not
replicated. To propagate this to the subscriber, creating a TSQL script and
running sp_addscriptexec is the way to go. The same thing could be achieved
by using linked servers if they were all online but using sp_addscriptexec
is easier and will make sure the script is applied to all subscribers when
they synchronize.
hth,
Paul Ibison
|||If I wanted to take the "long" way without utilizing sp_addscriptexec, would
this approach be valid:
- exec TSQL script on the publisher
- remote connect to subscribers and execute TSQL script
(2) I would not have to delete subscriptions or publications or disable
publishing in the above scenario?
Thanks Paul.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eV$$6kAdEHA.3616@.TK2MSFTNGP10.phx.gbl...
> Dropping and creating indexes on the publisher is permited but not
> replicated. To propagate this to the subscriber, creating a TSQL script
and
> running sp_addscriptexec is the way to go. The same thing could be
achieved
> by using linked servers if they were all online but using sp_addscriptexec
> is easier and will make sure the script is applied to all subscribers when
> they synchronize.
> hth,
> Paul Ibison
>
|||Robert,
yes - this works OK. Adding an index isn't treated as a table change in the
same way as adding a column, and is permitted on the publisher and
subscriber without affecting the replication setup.
Regards,
Paul Ibison