SQL Server INFORMATION_SCHEMA

Guys,
I was finding “How to determine whether a clumn has Unique Key Cnstraint in SQL SERVER”.
I learn the following things:
— using INFORMATION_SCHEMA:
1. List TABLEs
SELECT * FROM information_schema.tables WHERE TABLE_TYPE = ‘BASE TABLE’
(Note : Need to change quotes while executing,Use single quotes)
Any specific table(e.g.xyz)
SELECT * FROM information_schema.tables WHERE TABLE_TYPE = ‘BASE TABLE’ AND table_name = ‘xyz’
Any specific table(e.g.xyz) & specific database (e.g. abc)
SELECT * FROM information_schema.tables WHERE TABLE_TYPE = ‘BASE TABLE’ AND table_name = ‘xyz’ AND table_schema = ‘abc’

2. List VIEWs
SELECT * FROM information_schema.columns WHERE TABLE_TYPE = ‘VIEW’

3.List users
SELECT DISTINCT loginame FROM master..sysprocesses;

4.List INDICES
— using the sysindexes system view:
SELECT name FROM sysindexes WHERE OBJECT_NAME(id) = ‘xyz’;

5.List CONSTRAINTs
— with INFORMATION_SCHEMA:
Here’s the query that will return the names of the CONSTRAINTs defined in the ‘xyz’table:
SELECT constraint_name, constraint_type FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = ‘xyz’;

6. List table fields
Here’s the query that will return the names of the fields of the xyztable:
SELECT c.name FROM syscolumns c LEFT JOIN sysobjects o ON c.id = o.id WHERE o.name = ‘xyz’

7. List TRIGGERs
SELECT o.name FROM sysobjects o WHERE xtype = ‘TR’ AND OBJECTPROPERTY(o.id, ‘IsMSShipped’) =0 ;
List only the triggers for a given table ‘xyz’:
SELECT o.name FROM sysobjects o WHERE xtype = ‘TR’ AND OBJECTPROPERTY(o.id, ‘IsMSShipped’) = 0 AND object_name(parent_obj) = ‘xyx’;

8. List FUNCTIONs
— with INFORMATION_SCHEMA:
SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_type = N’FUNCTION’;

Query to list top 5 largest tables in the database
This examples gives the top 5 largest space occupying tables in the database along with it’s size in MB.

mysql> SELECT concat(table_schema,’.',table_name) table_name,
-> concat(round(data_length/(1024*1024),2),’M') data_length
-> FROM information_schema.TABLES
-> ORDER BY data_length DESC LIMIT 5;