Index Information

Also look at 

Table Information Script 

Database Information Script

USE master
GO

IF OBJECT_ID('sp_indexinfo') IS NOT NULL
 DROP PROCEDURE sp_indexinfo
GO

CREATE PROCEDURE sp_indexinfo @tblPat SYSNAME = '%'
 ,@missing_ix TINYINT = 0
AS
WITH key_columns
AS (
 SELECT c.OBJECT_ID
  ,c.NAME AS column_name
  ,ic.key_ordinal
  ,ic.is_included_column
  ,ic.index_id
  ,ic.is_descending_key
 FROM sys.columns AS c
 INNER JOIN sys.index_columns AS ic ON c.OBJECT_ID = ic.OBJECT_ID
  AND ic.column_id = c.column_id
 )
 ,physical_info
AS (
 SELECT p.OBJECT_ID
  ,p.index_id
  ,ds.NAME AS location
  ,SUM(CASE 
    WHEN a.type_desc = 'IN_ROW_DATA'
     THEN p.rows
    ELSE 0
    END) AS rows
  ,SUM(a.total_pages) AS pages
 FROM sys.partitions AS p
 INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id
 INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id
 GROUP BY OBJECT_ID
  ,index_id
  ,ds.NAME
 )
SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS sch_name
 ,OBJECT_NAME(i.OBJECT_ID) AS tbl_name
 ,i.NAME AS ix_name
 ,CASE i.TYPE
  WHEN 0
   THEN 'heap'
  WHEN 1
   THEN 'cl'
  WHEN 2
   THEN 'nc'
  WHEN 3
   THEN 'xml'
  WHEN 6
   THEN 'col-store'
  ELSE CAST(i.TYPE AS VARCHAR(2))
  END AS type
 ,i.is_unique AS uq
 ,CASE 
  WHEN is_primary_key = 0
   AND is_unique_constraint = 0
   THEN 'no'
  WHEN is_primary_key = 1
   AND is_unique_constraint = 0
   THEN 'PK'
  WHEN is_primary_key = 0
   AND is_unique_constraint = 1
   THEN 'UQ'
  END AS cnstr
 ,STUFF((
   SELECT CAST(', ' + kc.column_name + CASE kc.is_descending_key
      WHEN 0
       THEN ''
      ELSE ' DESC'
      END AS VARCHAR(MAX)) AS [text()]
   FROM key_columns AS kc
   WHERE i.OBJECT_ID = kc.OBJECT_ID
    AND i.index_id = kc.index_id
    AND kc.is_included_column = 0
   ORDER BY key_ordinal
   FOR XML PATH('')
   ), 1, 2, '') AS key_cols
 ,STUFF((
   SELECT CAST(', ' + column_name AS VARCHAR(MAX)) AS [text()]
   FROM key_columns AS kc
   WHERE i.OBJECT_ID = kc.OBJECT_ID
    AND i.index_id = kc.index_id
    AND kc.is_included_column = 1
   ORDER BY key_ordinal
   FOR XML PATH('')
   ), 1, 2, '') AS incl_cols
 ,p.rows
 ,p.pages
 ,CAST((p.pages * 8.00) / 1024 AS DECIMAL(9, 2)) AS MB
 ,s.user_seeks AS seeks
 ,s.user_scans AS scans
 ,s.user_lookups AS lookups
 ,s.user_updates AS updates
 ,CASE 
  WHEN i.is_disabled = 1
   THEN '[DISABLED]'
  ELSE p.location
  END AS location
 ,i.filter_definition AS filter --requires 2008
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IsDisabled') AS disabled
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IndexDepth') AS depth
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IndexFillFactor ') AS fill_factor
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IsPageLockDisallowed') AS page_lock_disallowed
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IsRowLockDisallowed') AS row_lock_disallowed
FROM sys.indexes AS i
LEFT JOIN physical_info AS p ON i.OBJECT_ID = p.OBJECT_ID
 AND i.index_id = p.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS s ON s.OBJECT_ID = i.OBJECT_ID
 AND s.index_id = i.index_id
 AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsMsShipped') = 0
 AND OBJECTPROPERTY(i.OBJECT_ID, 'IsTableFunction') = 0
 AND OBJECT_NAME(i.OBJECT_ID) LIKE @tblPat
ORDER BY tbl_name
 ,ix_name

DECLARE @crlf CHAR(2)

SET @crlf = CHAR(13) + CHAR(10)

IF @missing_ix = 1
BEGIN
 SELECT OBJECT_SCHEMA_NAME(d.OBJECT_ID) AS schema_name
  ,OBJECT_NAME(d.OBJECT_ID) AS table_name
  ,'CREATE INDEX ' + CAST(OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '__' + OBJECT_NAME(d.OBJECT_ID) + '__' + REPLACE(REPLACE(COALESCE(REPLACE(d.equality_columns, ', ', '__'), ''), '[', ''), ']', '') + '__' + REPLACE(REPLACE(COALESCE(REPLACE(d.inequality_columns, ', ', '__'), ''), '[', ''), ']', '') + '__' + REPLACE(REPLACE(COALESCE(REPLACE(d.included_columns, ', ', '__'), ''), '[', ''), ']', '') AS SYSNAME) + @crlf + 'ON ' + OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '.' + OBJECT_NAME(d.OBJECT_ID) + ' ' + '(' + COALESCE(d.equality_columns + COALESCE(', ' + d.inequality_columns, ''), d.inequality_columns) + ')' + @crlf + COALESCE('INCLUDE(' + d.included_columns + ')', '') AS ddl
  ,s.user_seeks
  ,s.user_scans
  ,s.avg_user_impact
 FROM sys.dm_db_missing_index_details AS d
 INNER JOIN sys.dm_db_missing_index_groups AS g ON d.index_handle = g.index_handle
 INNER JOIN sys.dm_db_missing_index_group_stats AS s ON g.index_group_handle = s.group_handle
 WHERE OBJECT_NAME(d.OBJECT_ID) LIKE @tblPat
  AND d.database_id = DB_ID()
 ORDER BY avg_user_impact DESC
END
GO

EXEC sp_MS_Marksystemobject sp_indexinfo

Also look at 

Table Information Script 

Database Information Script

Table Information

Also look at 

Index Information Script 

Database Information Script

USE master
GO

IF OBJECT_ID('sp_tableinfo') IS NOT NULL
 DROP PROCEDURE sp_tableinfo
GO

CREATE PROCEDURE sp_tableinfo @tblPat SYSNAME = '%'
 ,@sort CHAR(1) = 'm'
AS
WITH t
AS (
 SELECT SCHEMA_NAME(t.schema_id) AS schema_name
  ,t.NAME AS table_name
  ,SUM(CASE 
    WHEN p.index_id IN (
      0
      ,1
      )
     AND a.type_desc = 'IN_ROW_DATA'
     THEN p.rows
    ELSE 0
    END) AS rows
  ,SUM(CAST((a.total_pages * 8.00) / 1024 AS DECIMAL(9, 2))) AS MB
  ,SUM(a.total_pages) AS pages
  ,ds.NAME AS location
 FROM sys.tables AS t
 INNER JOIN sys.partitions AS p ON t.OBJECT_ID = p.OBJECT_ID
 INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id
 INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id
 WHERE t.NAME LIKE @tblPat
 GROUP BY SCHEMA_NAME(t.schema_id)
  ,t.NAME
  ,ds.NAME
 )
SELECT schema_name
 ,table_name
 ,rows
 ,MB
 ,pages
 ,location
FROM t
ORDER BY CASE 
  WHEN @sort = 'n'
   THEN table_name
  END
 ,CASE 
  WHEN @sort = 'r'
   THEN rows
  END DESC
 ,CASE 
  WHEN @sort = 'm'
   THEN MB
  END DESC
 ,CASE 
  WHEN @sort = 's'
   THEN schema_name
  END
GO

EXEC sp_MS_Marksystemobject 'sp_tableinfo'

Also look at 

Index Information Script 

Database Information Script

Database Information

Also look at 

Table Information Script 

Index Information Script 

USE master
GO

IF OBJECT_ID('sp_dbinfo') IS NOT NULL
 DROP PROCEDURE sp_dbinfo
GO

CREATE PROCEDURE sp_dbinfo @sort CHAR(1) = 'n'
 ,@include_instance_name CHAR(1) = 'n'
AS
/***************************************************************************
@sort accept 4 values: 'n' (default), 'd', 'l' and 'r'.
@include_server_name accept 2 values, 'y' and 'n'.
It specifies the sort order (name, data allocated, log allocated, rollup only).
***************************************************************************/
SET NOCOUNT ON

DECLARE @sql NVARCHAR(2000)
 ,@db_name SYSNAME
 ,@recovery_model VARCHAR(12)
 ,@crlf CHAR(2)

SET @crlf = CHAR(13) + CHAR(10)

--Create tables to hold space usage stats from commands
CREATE TABLE #logspace (
 database_name SYSNAME NOT NULL
 ,log_size REAL NOT NULL
 ,log_percentage_used REAL NOT NULL
 )

CREATE TABLE #dbcc_showfilestats (
 database_name SYSNAME NULL
 ,file_id_ INT NOT NULL
 ,file_group INT NOT NULL
 ,total_extents BIGINT NOT NULL
 ,used_extents BIGINT NOT NULL
 ,name_ SYSNAME NOT NULL
 ,file_name_ NVARCHAR(3000) NOT NULL
 )

--Create table to hold final output
CREATE TABLE #final_output (
 database_name SYSNAME
 ,data_allocated INT
 ,data_used INT
 ,log_allocated INT
 ,log_used INT
 ,is_sum BIT
 )

--Populate log space usage
INSERT INTO #logspace (
 database_name
 ,log_size
 ,log_percentage_used
 )
SELECT instance_name AS 'Database Name'
 ,MAX(CASE 
   WHEN counter_name = 'Log File(s) Size (KB)'
    THEN cntr_value / 1024.
   ELSE 0
   END) AS 'Log Size (MB)'
 ,MAX(CASE 
   WHEN counter_name = 'Percent Log Used'
    THEN cntr_value
   ELSE 0
   END) AS 'Log Space Used (%)'
FROM master..sysperfinfo
WHERE counter_name IN (
  'Log File(s) Size (KB)'
  ,'Percent Log Used'
  )
 AND instance_name != '_total'
GROUP BY instance_name

----Populate data space usage 
DECLARE db CURSOR
FOR
SELECT NAME
FROM sys.databases
WHERE state_desc = 'ONLINE'

OPEN db

WHILE 1 = 1
BEGIN
 FETCH NEXT
 FROM db
 INTO @db_name

 IF @@FETCH_STATUS <> 0
  BREAK

 SET @sql = 'USE ' + QUOTENAME(@db_name) + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'

 INSERT INTO #dbcc_showfilestats (
  file_id_
  ,file_group
  ,total_extents
  ,used_extents
  ,name_
  ,file_name_
  )
 EXEC (@sql)

 UPDATE #dbcc_showfilestats
 SET database_name = @db_name
 WHERE database_name IS NULL
END

CLOSE db

DEALLOCATE db

--Result into final table
INSERT INTO #final_output (
 database_name
 ,data_allocated
 ,data_used
 ,log_allocated
 ,log_used
 ,is_sum
 )
SELECT CASE 
  WHEN d.database_name IS NOT NULL
   THEN d.database_name
  ELSE '[ALL]'
  END AS database_name
 ,ROUND(SUM(CAST((d.data_alloc * 64.00) / 1024 AS DECIMAL(18, 2))), 0) AS data_allocated
 ,ROUND(SUM(CAST((d.data_used * 64.00) / 1024 AS DECIMAL(18, 2))), 0) AS data_used
 ,ROUND(SUM(CAST(log_size AS NUMERIC(18, 2))), 0) AS log_allocated
 ,ROUND(SUM(CAST(log_percentage_used * 0.01 * log_size AS NUMERIC(18, 2))), 0) AS log_used
 ,GROUPING(d.database_name) AS is_sum
FROM (
 SELECT database_name
  ,SUM(total_extents) AS data_alloc
  ,SUM(used_extents) AS data_used
 FROM #dbcc_showfilestats
 GROUP BY database_name
 ) AS d
INNER JOIN #logspace AS l ON d.database_name = l.database_name
INNER JOIN sys.databases AS sd ON d.database_name = sd.NAME
GROUP BY d.database_name
WITH ROLLUP

--Output result
SET @sql = '
SELECT f.database_name, f.data_allocated, f.data_used, f.log_allocated, f.log_used, d.recovery_model_desc' + CASE @include_instance_name
  WHEN 'y'
   THEN ', @@SERVERNAME AS instance_name'
  ELSE ''
  END + @crlf + 'FROM #final_output AS f LEFT OUTER JOIN sys.databases AS d ON f.database_name = d.name' + @crlf + CASE 
  WHEN @sort = 'r'
   THEN 'WHERE f.database_name = ''[ALL]'''
  ELSE ''
  END + @crlf + 'ORDER BY is_sum' + @crlf + CASE 
  WHEN @sort = 'n'
   THEN ', database_name'
  WHEN @sort = 'd'
   THEN ', data_allocated DESC'
  WHEN @sort = 'l'
   THEN ', log_allocated DESC'
  ELSE ''
  END

--PRINT @sql
EXEC (@sql)
 --Test execution
 /*
EXEC sp_dbinfo
EXEC sp_dbinfo 'n'
EXEC sp_dbinfo 'd'
EXEC sp_dbinfo 'l'
EXEC sp_dbinfo 'r'
EXEC sp_dbinfo 'n', 'y'
EXEC sp_dbinfo 'd', 'y'
*/
GO

EXEC sp_MS_marksystemobject 'sp_dbinfo'

Also look at 

Table Information Script 

Index Information Script 

Find the Instance Name , Port on which SQL Server is Listening

SQL Server is sometimes installed as named instances and sometimes will be made to sit on different ports.

The host name , Server name and the instance name can be identified by the SQL functions

SELECT SERVERPROPERTY('servername') ServerName
 ,SERVERPROPERTY('InstanceName') instancename
 ,SERVERPROPERTY('MachineName') AS HOSTNAME

For the Port number you can read it from the Registry or the ErrorLog

Registry:

SET NOCOUNT ON

DECLARE @key VARCHAR(1000)
 ,@PortNumber VARCHAR(20)

IF charindex('\', CONVERT(CHAR(40), SERVERPROPERTY('servername')), 0) <> 0
BEGIN
 SET @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' + @@servicename + '\MSSQLServer\Supersocketnetlib\TCP'
END
ELSE
BEGIN
 SET @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'
END

EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
 ,@key = @key
 ,@value_name = 'Tcpport'
 ,@value = @PortNumber OUTPUT

Error Log:

USE master
GO

xp_readerrorlog 0
 ,1
 ,N'Server is listening on'
GO

Quick Summary for Minimally Logged Operations ( Inserts and Merge)

INSERT PERFORMANCE:

Below is the summary of options that we can use for improving insert performance.

This is assuming the database is in simple or Bulk Recovery model.

Table Indexes	Rows in table	Hints	Without TF 610	With TF 610	Concurrent possible
Heap	Any	TABLOCK	Minimal	Minimal	Yes
Heap	Any	None	Full	Full	Yes
Heap + Index	Any	TABLOCK	Full	Depends (3)	No
Cluster	Empty	TABLOCK, ORDER (1)	Minimal	Minimal	No
Cluster	Empty	None	Full	Minimal	Yes (2)
Cluster	Any	None	Full	Minimal	Yes (2)
Cluster	Any	TABLOCK	Full	Minimal	No
Cluster + Index	Any	None	Full	Depends (3)	Yes (2)
Cluster + Index	Any	TABLOCK	Full	Depends (3)	No

Table 1: Summary of minimal logging conditions

(1) If you are using the INSERT … SELECT method, the ORDER hint does not have to be specified, but the rows must be in the same order as the clustered index. If using BULK INSERT the order hint must be used.

(2) Concurrent loads only possible under certain conditions. Also, only rows written to newly allocated pages are minimally logged.

(3) Depending on the plan chosen by the optimizer, the non-clustered index on the table may either be fully- or minimally logged.

MERGE PERFORMANCE:

The Transact-SQL MERGE command offers minimal logging of the inserted rows. Unless you run MERGE on a heap, this minimal logging ability will only be used if trace flag 610 is enabled.