Goosfraba

Finding Different Objects in the SQL Database 

1. Find All the Tables from the Database 
To select All the tables present in your Database run following query in query window.
-------------------------------------------------------------------------------------------------------------
USE [YourDB]
GO 
SELECT *
FROM sys.Tables
GO
-------------------------------------------------------------------------------------------------------------
This will return all the tables in the database which you have created.

2. Find All the Triggers present on the Database tables

To select All the triggers present on the  tables of your Database run following query in query window.

-------------------------------------------------------------------------------------------------------------

USE [YourDB]

SELECT OBJECT_NAME(parent_id) AS TableName,

name AS TriggerName,

create_date AS CreationDate,

modify_date AS ModifyDate

FROM sys.triggers

-------------------------------------------------------------------------------------------------------------

This will return all triggers present on the tables of your database.

3. Find All the Indexes present on Columns of the Database tables

To select All the Indexes present on the  data columns of tables of your Database run following query in query window.
---------------------------------------------------------------------------------------------------------------------------------------------

SELECTs.name AS  'Schema', t.name AS  'Table', i.name AS  'Index', c.name AS  'Column'
FROM sys.Tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
ic.column_id = c.column_id
where i.index_id > 0    
and i.type in (1, 2) -- clustered & nonclustered only
and  i.is_primary_key = 0 -- do not include PK indexes
and  i.is_unique_constraint = 0 -- do not include UQ
and  i.is_disabled = 0
and  i.is_hypothetical = 0
and  ic.key_ordinal > 0
order by ic.key_ordinal
---------------------------------------------------------------------------------------------------------------------------------------------
This will return all indexes present on the columns of the tables of your database.
While, list of schema names and table names of the database can be retrieved by following query
-----------------------------------------------------------------------------------------------------------------

Use [YourDB]
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable

-----------------------------------------------------------------------------------------------------------------


Thanks for reading this post, hopefully it would have proved instrumental for you.