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?
>

indexed views and context

Hi guys ,

we had data in tables for multiple users (Logins) .Each user data is identified by a one column named “USER”. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?

If yes how can I implement .Because data depends on context is there any way to create global indexed views.

In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

Hi Rama,

You can use the option EXPAND VIEWS in SELECT clause for your purpose.

Microsoft recommend's, however, to let the query optimizer dynamically determine the best access methods to use for each individual query

Jag

|||

Can u explain me in detail.if possible with example becz i'm new to DB stuff.

indexed views and context

Hi guys ,

we had data in tables for multiple users (Logins) .Each user data is identified by a one column named “USER”. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?

If yes how can I implement .Because data depends on context is there any way to create global indexed views.

In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

First of all you can't create a index on your view.

Reason: suser_sname is not a deterministic function. So it wont to allow you to create a index on this..

|||

Can u explain me in detail .I’m new to indexed views.

Can we create indexed view for all data in tables and can be filtered this data by referencing this indexed view with in another view? Can u consider this point also.

If this is possible then, is there any way restrict not to use indexed views by query optimizer .