Avatar of ksd123
ksd123
 asked on

List of all Database objects in SQL server 2005 / 2000

Hi,

I would like to know

1)list of all database objects names (SP's,views,triggers/tables) in sql server 2005 /2000

2)How to find last execution / accessed  time of all database objects in sql server 2005 and sql server 2000 .

3)How to find if a store d procedure is compiled or not?

Thanks in Advance
Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
ksd123

8/22/2022 - Mon
SOLUTION
jogos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ksd123

ASKER
Sorry for the confusion, I need to know list all objects of a particular database in SQL Server 2005  and 2000
jogos

And where do my links fail your question? You get different examples of what you can do with it.


For SQL2005 (modify_date you don't find in the sql2000-version)
USE <database_name>;
GO
SELECT name AS object_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
ORDER BY modify_date;
GO

Open in new window

ksd123

ASKER
I have tried to execute above script in sql server 2005, its giving

'SCHEMA_NAME' is not a recognized function name.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
jogos

It's a 2005-function
http://msdn.microsoft.com/en-us/library/ms175068(v=sql.90).aspx

Maybe your database is still compatibility-level 80 (sql2000)
select * from sys.databases
=> see if compatibility-level = 90 for your database.
ksd123

ASKER
Thanks , I just executed in sql server 2005  , we have information about object_name
    ,create_date
  ,modify_date .

Is there any way if we can get last accessed / execution date time  of a dabase object  in sql server 2005?
jogos

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ksd123

ASKER
I am not familiar with sql server can you assist me which script in the above link  should I execute to know last execution date time of a table / SP ?
ASKER CERTIFIED SOLUTION
jogos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ksd123

ASKER
Sorry to bother you, here object name means table / sp right? should I give name of the table or SP and aslo in where condition  do I need to  give databaseid db_id(database id)
jogos

db_id()  will return the id of the current database if you don't give a name. So you must see that the correct db is current.
tutorial http://msdn.microsoft.com/en-us/library/ms186274.aspx

Object name is indeed table , view , ..
Just execute the sql without a name and you'll see what you get..
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ksd123

ASKER
Thank you