Main frequently asked interview questions in SQL Server

In this article I will explain How to get stored procedure list in SQL Server? , How to get Tables list in SQL Server? , how to get list of views in SQL Server?

The main frequently asked interview questions in SQL Server are

1.      How to get stored procedure list in SQL Server?

2.      How to get Tables list in SQL Server?

3.      How to get list of views in SQL Server?

Below queries are useful for getting the Stored Procedure list in SQL Server: 4 ways to get the Stored Procedure list

1.   select *   from DataBaseName.information_schema.routines  where routine_type =                              'PROCEDURE'

2.      select *  from information_schema.routines  where routine_type = 'PROCEDURE'

3.      select *   from DataBaseName.information_schema.routines  where routine_type = 'PROCEDURE'    and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')

4.      SELECT name,   type  FROM dbo.sysobjects WHERE (type = 'P')

Below query is useful for getting the Views list in SQL Server:

SELECT * FROM sys.views

Below queries are useful for getting the SQL Tables list:

SQL Server 2005 or 2008 and above:

SELECT * FROM information_schema.tables

SQL Server 2000:

SELECT * FROM sysobjects WHERE xtype='U'