List of all Database objects in SQL server 2005 / 2000

ksd123
ksd123 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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

Commented:
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

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

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

'SCHEMA_NAME' is not a recognized function name.

Commented:
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.

Author

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

Author

Commented:
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 ?
Commented:
Sql-queries are kept in the cache (but not always) and that's the place where you can find where a object is last used by a cached query. Just put the objectname between the %% on last line and it searches with that string in the query-text.
Not perfect but as I said your best shot.

-- Find object in cache
SELECT qt.[text] AS [SP Name], qs.last_execution_time, qs.execution_count AS [Execution Count] 
	FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
	WHERE qt.dbid = db_id() -- Filter by current database
	AND qt.text LIKE '%objectname%'

Open in new window

Author

Commented:
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)

Commented:
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..

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial