MS SQL Server Admin - Queries

Tables and Views

To get all tables, views, and system tables, the following SQL Server system stored procedure can be executed.

exec sp_tables '%'

To filter by database for tables only, for example master:

exec sp_tables '%', '%', 'master', "'TABLE'"

To filter by database and owner / schema for tables only, for example, master and dbo:

exec sp_tables '%', 'dbo', 'master', "'TABLE'"

To return only views, replace "'TABLE'" with "'VIEW'". To return only system tables, replace "'TABLE'" with "'SYSTEM TABLE'".

Schemas / Owners

Here are two examples for queries to get schema / owner information.

select distinct SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA order by SCHEMA_NAME

select name from dbo.sysusers where islogin = 1 order by name

Procedures

This is a query to get all MS SQL Server procedures.

exec sp_stored_procedures '%'

The query can be filtered to return procedures for specific schemas / owners and databases by appending more information onto the procedure call, such as the following:

exec sp_stored_procedures '%', 'dbo', 'master'

Procedure Columns

This is a system stored procedure call to get the columns in a SQL Server procedure.

exec sp_sproc_columns 'get_employee_names', 'dbo', 'sample'

Functions

This is a query to get all MS SQL Server functions.

select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where upper(ROUTINE_TYPE) = 'FUNCTION'

Triggers

This is a query to get all MS SQL Server triggers.

select * from sysobjects where type = 'TR'

The query can be filtered to return triggers for a specific owner by appending a user_name call onto the where clause to the query.

select * from sysobjects where type = 'TR' and user_name(sysobjects.uid) = 'dbo'

Indexes

This is a query to get MS SQL Server indexes for a particular table. In this example, the table used is employee.

exec sp_helpindex 'employee'