Wednesday, March 26, 2014

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'



No comments: