Tsql tips and Tricks

To list all the tables in a database
Select * from sys.tables where type='U'

or

Select * from sys.tables

To count the number of columns in a table

SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'DOCPREPREPORTREQUEST'

To Count the Number of tables in a Database

Select count(*) from information_schema.tables where table_type='base table'

To Count the number of columns in each table in a database

SELECT TABLE_NAME, COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS GROUP BY TABLE_NAME

Finding the table of a particular column

Select object_name(object_id) as Tablename, * from sys.columns where name like '%Audittrailcolumn%'

Here Audittrailcolumn is the name of the column

To list all the Stored Procedures in a Database

SELECT name FROM sys.objects WHERE type = 'P'

To know when a table was last modified

select * from sys.tables where type='u' and name='Securityprofile'

Finding the primary and foreign keys of all the tables in a database

SELECT DISTINCT Constraint_Name AS [Constraint],Table_Schema AS [Schema],Table_Name AS [TableName]FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Display the table names and their corresonding rowcounts

http://blog.sqlauthority.com/2009/01/13/sql-server-find-row-count-in-table-find-largest-table-in-database-t-sql/