SQLSERVER 2000 Tips

In sqlserver 2000, unlike other database management systems, there is no direct way to display database objects, such as tables, views, and user defined functions.

I searched on net for this but found nothing helpful. Then I went to sqlserver 2000 local help (the only resource much helpful for me when working with sqlserver) and found a stored-procedure in master table, sp_help. After studying a bit about it and first portion of the procedure, I made the following queries for getting specific database objects.

  • List all user tables in current database using SQL:
    select name from dbo.sysobjects where xtype=’U’ order by name
  • List all views in current database using SQL:
    select name from dbo.sysobjects as Views where xtype=’V’ order by name
  • List all User-Defined-Store-Procedures using SQL:
    select name from dbo.sysobjects as UserStoreProcedures where xtype=’P’ and category=0 order by name
  • List all user defined functions using SQL:
    select name from dbo.sysobjects as Views where xtype=’FN’ order by name

Please comment if you find it useful or have a better way for getting the objective. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: