How to Select/Display/List tables from MS SQL Server 2008 R2 ?

Some time we need to select list of user table in the Database to identify the table list. For that we have different SQL querys to get display the Table list. Here I am give some small code to display the user table in different SQL query language.

List out the Data Base Tables detail:

SELECT * FROM sys.Tables

SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT sc.name +'.'+ ta.name TableName ,SUM(pa.rows) RowCnt

FROM sys.tables ta

INNER JOIN sys.partitions pa  ON pa.OBJECT_ID = ta.OBJECT_ID

INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id

WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)

GROUP BY sc.name,ta.name ORDER BY SUM(pa.rows) DESC

How to Select / Display / List Views in the MS SQL Server 2008 R2:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'view'

How to Select / Display / List Table Columns in the MS SQL Server 2008 R2:

SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='MyTable'