How do i grab a simple list of SQL Server views, tables or sprocs?

[Kofi]

I need just the names of tables, views and sprocs within a SQL Server
database. What's the easiest way to do this?

[Erland Sommarskog]

[posted and mailed, please reply in news]

Kofi (ko*******@gmail.com) writes:

I need just the names of tables, views and sprocs within a SQL Server
database. What's the easiest way to do this?

SELECT name FROM sysobjects WHERE xtype = 'U' -- Tables
SELECT name FROM sysobjects WHERE xtype = 'V' -- Views
SELECT name FROM sysobjects WHERE xtype = 'P' -- Stored Procedures


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Gilson]

"Kofi" <ko*******@gmail.com> wrote in message
news:5c**************************@posting.google.c om...

I need just the names of tables, views and sprocs within a SQL Server
database. What's the easiest way to do this?

SELECT table_name AS object_name,
table_schema AS schema_name,
table_type AS object_type
FROM INFORMATION_SCHEMA.TABLES
UNION ALL
SELECT routine_name AS object_name,
routine_schema AS schema_name,
routine_type AS object_type
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_type = 'PROCEDURE'
ORDER BY object_type, schema_name, object_name

--
JAG

[Kofi]

Thanks Erland,

I was having a thick moment. I've used those SELECT statements
countless times before. Shame I was looking at the Master database
rather than the database I was in fact interested in.

But now I've a nice alternative below though :)

-- Kofi

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...

[posted and mailed, please reply in news]

Kofi (ko*******@gmail.com) writes:

I need just the names of tables, views and sprocs within a SQL Server
database. What's the easiest way to do this?

SELECT name FROM sysobjects WHERE xtype = 'U' -- Tables
SELECT name FROM sysobjects WHERE xtype = 'V' -- Views
SELECT name FROM sysobjects WHERE xtype = 'P' -- Stored Procedures