Cómo buscar objetos de base de datos consultando tablas de sistema (SQL SERVER)

/* ================================
Get the list of all databases
================================ */

select * from sys.databases — SQL Server 2005
Select * from sysdatabases — SQL Server 2000

–Tip:When you are in SQL Server Management Studio or Query Analyzer, Press Ctrl + U, it will activate drop down will all user defined databases.

/* ================================
Get Current Database Name
================================ */
Select Db_Name()

/* ================================
Get list of database files for a specific database
================================ */

Select * from sys.sysfiles — sql server 2005
or
select * from sysfiles — sql server 2000

/* ================================
Get the list of all logins in a server
================================ */

Select * from syslogins — sql server 2005
or
select * from sysxlogins — sql server 2000

/* ================================
Get Current logged in login Name
================================ */

Select suser_sname()

/* ================================
Get the list of all users in a database
================================ */
Select * from Sysusers — sql server 2000
Select * from sys.Sysusers — SQL Server 2005

/* ================================
Get Current logged in User Name
================================*/
Select user_name()

/* ====================================
Get a list of all objects in a database
=======================================*/

Select * from sysobjects — sql server 2000
or
Select * from sys.all_objects — SQL Server 2005

/* ================================
Get list of all user defined tables.
=====================================*/

Select * from information_schema.tables
or
Select * from sys.tables — SQL Server 2005

/* ================================
To get list of all views
================================*/
Select * from sys.views order by create_Date desc — SQL Server 2005

/* ================================
To get list of all procedures,
================================*/

Select * from sys.procedures order by create_Date desc — SQL Server 2005
/*================================
To get list of columns
================================*/
Select * from Information_Schema.columns

/*================================================================
Get the definition (Original text) of procedure, view, function.
================================*================================*/
Exec Sp_Helptext Object_Name
or
Exec Sp_Helptext [owner.objectname]

/*================================================================================================
Get Table ,View structure, information about indexes, constraints, data type, data length.
================================================================================================*/
Exec Sp_Help object_name
or
Exec Sp_Help [owner.object_name]

/*================================================================
Get all dependency on a particular object.
================================================================*/
Sp_depends object_name

/*================================================================
Get list of orphaned (sql server) users.
================================================================*/
Sp_change_users_login ‘report’ :

/* ================================================================
Get Object Level Permision for each user or db_role
================================================================*/
sp_helprotect
or
sp_helprotect @user_name = ‘name of the user’

-----------------------------------------------

Pagina fuente: http://blog.sqlauthority.com/2007/05/25/sql-server-stored-procedure-to-display-code-text-of-stored-procedure-trigger-view-or-object/