Some common used SQL Scripts

-- List of sp's
Select [NAME] from sysobjects where type = 'P' and category = 0

-- List of views
Select [NAME] from sysobjects where type = 'V' and category = 0

-- List of functions
SELECT ROUTINE_NAME FROM information_schema.routines WHERE routine_type = 'function'
order by ROUTINE_NAME

-- list of tables
SELECT  sd.source_database_id AS [SourceDatabase],
        sd.name AS [Snapshot],
        mf.name AS [Filename],
        size_on_disk_bytes/1024 AS [size_on_disk (KB)],
        mf2.size/128 AS [MaximumSize (MB)]
FROM sys.master_files mf
JOIN sys.databases sd
    ON mf.database_id = sd.database_id
JOIN sys.master_files mf2
    ON sd.source_database_id = mf2.database_id
    AND mf.file_id = mf2.file_id
CROSS APPLY sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id)
WHERE mf.is_sparse = 1
AND mf2.is_sparse = 0
ORDER BY 1;

-- Constraints info
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT%'
--WHERE type_desc LIKE '%PRIMARY_KEY_CONSTRAINT%'
--WHERE type_desc LIKE '%FOREIGN_KEY_CONSTRAINT%'
--WHERE type_desc LIKE '%DEFAULT_CONSTRAINT%'
GO

-- Tables with columns
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;

-- Top ten stored procedures identified by average elapsed time.
SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',  
    d.cached_time, d.last_execution_time, d.total_elapsed_time, 
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time], 
    d.last_elapsed_time, d.execution_count 
FROM sys.dm_exec_procedure_stats AS d 
ORDER BY [total_worker_time] DESC;

-- Queries doing most I/O
SELECT TOP 100
    (total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
    (total_logical_reads + total_logical_writes) AS total_IO,
    qs.execution_count AS execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
  FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
  ORDER BY average_IO DESC;