If you want to count the number of rows across all the tables in a database ,select count(*) for all the tables is quite inefficient method.Better approach is as under

SELECT
    sysobjects.Name
    , sysindexes.Rows
FROM
    sysobjects
    INNER JOIN sysindexes
    ON sysobjects.id = sysindexes.id
WHERE
    type = 'U'
    AND sysindexes.IndId < 2
ORDER BY
    sysobjects.Name

 

Overview

During the development cycle of a project, persons related to the project need to know the information about the database objects like how many tables/ columns/ views etc. are there? For the documentation purpose, it also needs to document about the meta data of the project database.

For this purpose SQL Server is facilitated with a very easy command called INFORMATION_SCHEMA.The INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database.  These views can be found in the master database under Views / System Views and be called from any database in your SQL Server instance.  The reason these were developed was so that they are standard across all database platforms.  In SQL 2005 and SQL 2008 these Information Schema views comply with the ISO standard.

List of INFORMATION_SCHEMA views and uses

Following are the details of INFORMATION_SCHEMA views and uses:

INFORMATION_SCHEMA.CHECK_CONSTRAINTS: Get information about the specific data rules

The INFORMATION_SCHEMA.CHECK_CONSTRAINTS view allows you to get information about the check constraints that are setup in your database.  A check constraint is a constraint put on a particular column in a table to ensure that specific data rules are followed for a column.

USE AdventureWorks
Go
select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
GO

OUTPUT:

INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE: Get information about alias data types that are used for columns

The INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE view allows you to get information about alias data types that are used for columns By default it will show you this information for every single table and view that is in the database that uses an alias data type.

USE AdventureWorks
GO
Select * From INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
GO

OUTPUT:

INFORMATION_SCHEMA.COLUMN_PRIVILEGES: Get information about privilege on columns

The INFORMATION_SCHEMA.COLUMN_PRIVILEGES view returns one row for each column that has a privilege that is either granted to or granted by the current user in the current database.

USE AdventureWorks
GO
Select * From INFORMATION_SCHEMA.COLUMN_PRIVILEGES
GO

INFORMATION_SCHEMA.COLUMNS: Get information about all columns for all tables and views

The INFORMATION_SCHEMA.COLUMNS view allows you to get information about all columns for all tables and views within a database. By default it will show you this information for every single table and view that is in the database

USE AdventureWorks
GO
Select * From INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Customer'
GO

INFORMATION_SCHEMA.ROUTINES: Get information about all user defined functions and stored procedures

The INFORMATION_SCHEMA.COLUMNS view allows you to get information about user defined functions and stored procedures within a database. By default it returns one row for each stored procedure and function that can be accessed by the current user in the current database. The columns that describe the return value apply only to functions. For stored procedures, these columns will be NULL.

USE AdventureWorks
GO
Select ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, ROUTINE_DEFINITION,CREATED, LAST_ALTERED 
From INFORMATION_SCHEMA.ROUTINES
GO

INFORMATION_SCHEMA.VIEWS: Get information about all views

The INFORMATION_SCHEMA.VIEWS allows you to get information about views within a database. By default it Returns one row for views that can be accessed by the current user in the current database.

USE AdventureWorks
GO
Select * From INFORMATION_SCHEMA.VIEWS
GO

Other useful views:

INFORMATION_SCHEMA.[View Name]	Uses
CONSTRAINT_COLUMN_USAGE	Returns one row for each column in the current database that has a constraint defined on the column. This information schema view returns information about the objects to which the current user has permissions.
CONSTRAINT_TABLE_USAGE	Returns one row for each table in the current database that has a constraint defined on the table. This information schema view returns information about the objects to which the current user has permissions.
DOMAIN_CONSTRAINTS	Returns one row for each alias data type in the current database that has a rule bound to it and that can be accessed by current user.
DOMAINS	Returns one row for each alias data type that can be accessed by the current user in the current database.
KEY_COLUMN_USAGE	Returns one row for each column that is constrained as a key in the current database. This information schema view returns information about the objects to which the current user has permissions.
PARAMETERS	Returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information.
REFERENTIAL_CONSTRAINTS	Returns one row for each FOREIGN KEY constraint in the current database. This information schema view returns information about the objects to which the current user has permissions.
ROUTINE_COLUMNS	Returns one row for each column returned by the table-valued functions that can be accessed by the current user in the current database.
SCHEMATA	Returns one row for each schema in the current database.
TABLE_CONSTRAINTS	Returns one row for each table constraint in the current database. This information schema view returns information about the objects to which the current user has permissions.
TABLE_PRIVILEGES	Returns one row for each table privilege that is granted to or granted by the current user in the current database.
VIEW_COLUMN_USAGE	Returns one row for each column in the current database that is used in a view definition. This information schema view returns information about the objects to which the current user has permissions.
VIEW_TABLE_USAGE	Returns one row for each table in the current database that is used in a view. This information schema view returns information about the objects to which the current user has permissions.

For more information, visit MSDN link http://msdn.microsoft.com/en-us/library/ms186778.aspx.