Query to find IP, port , connection details of SQL server

SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

List all the foreign keys referencing a database in SQL Server

SELECT  obj.name AS FK_NAME,   sch.name AS [schema_name],
    tab1.name AS [table],  col1.name AS [column],
    tab2.name AS [referenced_table],col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj     ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1     ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch     ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1     ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2     ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2     ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

T-SQL Script to know current sessions status in SQL Server

Use the below query to get the current SQL Server status with blocking and high CPU ordered, Use the host-name and database name to drill down.

SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id 'blocked by'
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'Wait Time (in Sec)'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
     Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
cross APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID 
--and s.host_name in ('SUPERMAN','BATMAN') 
--and dbid=DB_ID('DBAAdmin')
--and       r.cpu_time > 1000000
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,s.session_id