SQL server - schema

views:

answers:

SQL server - schema

We can see schema for all tables and views by:

SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT * FROM INFORMATION_SCHEMA.VIEWS

Can we view schema for stored procedures or functions through tsql?

+4 A:

In other Information Schema Views, like INFORMATION_SCHEMA.ROUTINES:

Returns one row for each stored procedure and function that can be accessed by the current user in the current database

Remus Rusanu 2009-09-28 22:41:47

Thanks-Related to this:If a SQL agent job is running a stored procedure with a schema say 'xschema' and it runs other stored procedures with dbo schema. Under what circumstances, this main stored procedure woint find one of the child procedures? ( this is enough information?)thanks in advance

Manjot 2009-09-28 23:19:35

If a SQL Agent job is running dbo.usp_foo and this in turn invokes myschema.usp_foo and fails with an error `Cannot find the object myschema.usp_foo because it does not exist or you do not have permissions.` the the problem is most likely related to permissions, the account under which the job step is run doesn't have execute permission on `myschema.usp_foo`.

Remus Rusanu 2009-09-29 00:15:04

Thanks for your help

Manjot 2009-09-29 00:45:51