SQL SERVER DBA, Linux and Azure

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

How to display execution plans present in procedure cache in SQL Server

SELECT cp.objtype AS PlanType,
       OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
       cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,
       st.text AS SQLBatch,qp.query_plan AS QueryPlan
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;

    GO

How to get the complete backups information of a particular database in SQL Server

SELECT s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name, s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'AdventureWorks' -- Need to change the database name
ORDER BY database_name, backup_start_date, backup_finish_date

Dump a specific table or few rows in MySQL Server

The 'mysqldump' command is used to dump databases managed by MySQL. Let's consider three the most useful cases of MySQL database dumping.

The simplest case is the whole database dumping:

mysqldump -u username -ppassword database_name  > the_whole_database_dump.sql

Sometimes, there's a need to dump a single table from your database. You can do it in the next way:

mysqldump -u username -ppassword database_name table_name > single_table_dump.sql

You can also specify several tables separated by whitespace to dump these tables only.

If you want to dump only rows that meet a specific criteria, you can add 'where' option to your mysqldump command. For example, dump only rows where date_created is today:

mysqldump -u username -ppassword database_name table_name --where="date_created='2013-06-25'" > few_rows_dump.sql


                                                                                             

                                                                                                                 Prepared by Ajeyudu Bandaru