SqlDbAid DBA tool

Latest version (v2.6.1.7 - 2021-02-22) SqlDbAid.zip

SqlDbAid is an intuitive standalone application that will enable you to easily script database items and data.

SqlDbAid is free for personal and commercial usage.

Main Features

• SQL Server 2005+ support
• Text search inside code definition with highlitghting
• DBA reports (missing indexes, missing foreign key indexes, indexes status, top queries, table MBytes)
• One file per object or single file scripts (tables, views, triggers, procedures, functions, indexes...)
• Select, Insert and Update scripts
• Table data insert script creation
• Table/View data export
• Offline database compare tool

Requirements

• .Net Framework 4.5+

For questions and suggestions write to Miken.

Columns Inconsintency Check

Another hopefully useful script  from my toolbox.

Recently I had to check a quite denormalized database at the end of its development phase.

Frequently data types, length and other field attributes change during development, so I wrote a simple script that highlights possible mistakes (same column name but different definition).

UPDATE: system objects skipped

;WITH cte
AS (
 SELECT c.NAME cl_name
  ,Schema_name(t.schema_id) + '.' + t.NAME tb_name
  ,p.NAME [type]
  ,c.max_length
  ,c.scale
  ,c.PRECISION
 FROM sys.tables t
 INNER JOIN sys.columns c ON c.object_id = t.object_id
 INNER JOIN sys.types p ON p.user_type_id = c.user_type_id
  AND p.system_type_id = c.system_type_id
 WHERE
  NOT (
     EXISTS (SELECT 1 FROM sys.extended_properties EP WHERE EP.major_id = t.object_id AND EP.name = 'microsoft_database_tools_support')
     OR t.is_ms_shipped = 1
  )
 )
SELECT DISTINCT x.*
FROM cte x
INNER JOIN cte y ON y.tb_name != x.tb_name
 AND y.cl_name = x.cl_name
 AND (
  y.max_length != x.max_length
  OR y.PRECISION != x.PRECISION
  OR y.scale != x.scale
  OR y.[type] != x.[type]
  )
ORDER BY 1

Active Processes

ActiveProcesses.zip

This time I'll show you a script developed to cature running processes details.

There are a number of similar scripts out of there, but I've tried to use a single query based on DMVs only (no more sys.sysprocesses).

Exluding the deprecated sys.sysprocesses was the only tricky task, because there is no straight way to obtain the database_id unless we are using Sql Server 2012 which added this column to sys.dm_exec_sessions.

Fortunately sys.dm_tran_locks contains the information we need.

SET TRANSACTION isolation level READ uncommitted

SELECT CASE
         WHEN ER.total_elapsed_time > 0 THEN
         CONVERT(VARCHAR, (ER.total_elapsed_time - ER.total_elapsed_time %
         3600000) /
         3600000)
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         ER.total_elapsed_time
         % 60000) % 3600000 /
         60000), 2)
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         ER.total_elapsed_time
         % 1000) % 60000 / 1000), 2)
         + '.'
         + RIGHT('000' + CONVERT(VARCHAR, ER.total_elapsed_time % 1000), 3)
       END
       elapsed_time,
       ES.session_id,
       NULLIF(ER.blocking_session_id, 0)
       blocking_session_id,
       ES.[status],
       Db_name(COALESCE(ER.database_id, TL.resource_database_id, 1))
       database_name,
       ER.cpu_time,
       ER.reads,
       ER.writes,
       ER.logical_reads,
       ER.wait_time,
       ER.wait_type,
       ER.last_wait_type,
       ES.total_elapsed_time,
       ES.cpu_time
       total_cpu_time,
       ES.reads                                                      total_reads
       ,
       ES.writes
       total_writes,
       ES.logical_reads
       total_logical_reads,
       CASE
         WHEN Isnull(ER.open_transaction_count, 0) > 0 THEN
         ER.open_transaction_count
         WHEN ST.open_tran > 0 THEN ST.open_tran
       END
       open_transaction_count,
       CASE Isnull(ER.transaction_isolation_level,
            ES.transaction_isolation_level)
         WHEN 0 THEN 'Unspecified'
         WHEN 1 THEN 'ReadUncomitted'
         WHEN 2 THEN 'ReadCommitted'
         WHEN 3 THEN 'Repeatable'
         WHEN 4 THEN 'Serializable'
         WHEN 5 THEN 'Snapshot'
       END
       transaction_isolation,
       AT.name
       transaction_name,
       CASE AT.transaction_type
         WHEN 1 THEN 'read/write'
         WHEN 2 THEN 'read-only'
         WHEN 3 THEN 'system'
         WHEN 4 THEN 'distributed'
       END
       transaction_type,
       CASE AT.transaction_state
         WHEN 0 THEN 'not completely initialized'
         WHEN 1 THEN 'initialized but not started'
         WHEN 2 THEN 'active'
         WHEN 3 THEN 'read-only transaction has ended'
         WHEN 4 THEN 'commit initiated'
         WHEN 5 THEN 'prepared and waiting resolution'
         WHEN 6 THEN 'committed'
         WHEN 7 THEN 'being rolled back'
         WHEN 8 THEN 'been rolled back'
       END
       transaction_state,
       ES.last_request_start_time,
       ES.last_request_end_time,
       ES.[host_name],
       ES.login_time,
       ES.login_name,
       ES.[language],
       ES.date_format,
       ES.date_first,
       ES.[program_name],
       ER.command,
       ET.[text]
       sql_statement
FROM   sys.dm_exec_connections EC
       INNER JOIN sys.dm_exec_sessions ES
               ON ES.session_id = EC.session_id
       LEFT JOIN sys.dm_exec_requests ER
              ON ER.session_id = EC.session_id
       LEFT JOIN (SELECT Count(*) open_tran,
                         XT.session_id
                  FROM   sys.dm_tran_session_transactions XT
                  GROUP  BY XT.session_id) ST
              ON ST.session_id = ES.session_id
       LEFT JOIN sys.dm_tran_active_transactions AT
              ON AT.transaction_id = ER.transaction_id
       OUTER apply sys.Dm_exec_sql_text(Isnull(ER.sql_handle,
                                        EC.most_recent_sql_handle)) ET
       OUTER apply (SELECT TOP 1 XL.resource_database_id
                    FROM   sys.dm_tran_locks XL
                    WHERE  XL.resource_type = 'DATABASE'
                           AND XL.request_session_id = EC.session_id) TL
WHERE  EC.most_recent_sql_handle > 0x00
ORDER  BY ER.total_elapsed_time DESC,
          EC.session_id

This script will be included in the next version of SqlDbAid.

Database Permissions Script

DatabasePermissions.zip

This time I want to share a simple script to extract database permissions.
The code is based on sys.database_persmissions system view.
It basically contains the object ids, principal ids and related permissions (GRANT, DENY, ...).

SELECT
 CASE
  WHEN P.class IN (0, 3) THEN P.class_desc
  ELSE O.type_desc
 END type_desc,
 CASE
  WHEN P.class IN (0, 3) THEN ''
  ELSE SCHEMA_NAME(O.schema_id)
 END object_schema,
 CASE
  WHEN P.class = 3 THEN SCHEMA_NAME(P.major_id)
  WHEN P.class = 1 THEN OBJECT_NAME(P.major_id)
  ELSE ''
 END object_name,
 ISNULL(C.name, '') column_name,
 P.class_desc,
 USER_NAME(P.grantee_principal_id) grantee,
 P.permission_name,
 P.state_desc,
 USER_NAME(P.grantor_principal_id) grantor,
 CASE P.state
  WHEN 'W' THEN 'GRANT'
  ELSE P.state_desc
 END + ' ' +
 CASE
  WHEN P.class = 0 THEN P.permission_name + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id)) COLLATE database_default
  WHEN P.class = 3 THEN P.permission_name + ' ON SCHEMA::' + QUOTENAME(SCHEMA_NAME(P.major_id)) + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
  WHEN P.minor_id <> 0 THEN P.permission_name + ' ON ' + QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) + ' (' + QUOTENAME(C.name) + ') TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
  ELSE P.permission_name + ' ON ' + QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
 END +
 CASE P.state
  WHEN 'W' THEN ' WITH GRANT OPTION'
  ELSE ''
 END script
FROM
 sys.database_permissions P
  LEFT JOIN
 sys.all_objects O ON
  O.object_id = P.major_id
  LEFT JOIN
 sys.all_columns C ON
  C.object_id = P.major_id AND
  C.column_id = P.minor_id
WHERE
 NOT
 (
  P.class = 1 AND
  (
   EXISTS (SELECT TOP 1 1 FROM sys.extended_properties EP WHERE EP.major_id = O.object_id AND EP.name = 'microsoft_database_tools_support') OR
   O.is_ms_shipped = 1
  )
 )
ORDER BY
 1,2,3,4,5,6,7,8

Free SQL database compare tool

Hi All, SqlDbAid now comes with a new database compare module.


The comparison uses an offline mode like the previous version,
but now you'll be able to visually analyze the differences.


All you have to do is connect to two databases, select the objects
you want to examine and then create two compare exports.


These files will be the input for the compare module.

MKST Staging Area Template

MKST provides a standard approach to many of the common data warehousing activity tasks.


The data warehousing theory is out of scope here, but we could introduce some very basic concepts.


Most of the times we need to prepare data for a reporting/analysis tool without accessing directly the production databases:
- We don’t want to load the server interfering with every day user’s activities
- Normally source data is quite normalized (not ready for efficient reporting queries)
- We may need to do some cleansing and checks
- Many times we have to combine data from different sources (ERP, CRM…) and enrich them


MKST will enable you to transfer data from different sources (local or linked databases) to a data warehouse.


The MKST data warehouse flow consists of a process organized in three phases.
- Upload: transfer data from multiple sources to MKST staging
- Validation: check the uploaded data
- Import: transfer data from the staging area to a DWH


After configuring the staging the dbo.full_process stored procedure will do the job and the dbo.last_process_log will provide you the results.

MKST comes with four sql schemas.
- dbo: basic tables and procedures you should not modify
- ext: tables and procedure you may need to extend or add
- stg: tables that will serve as a destination for the original data
- dwh: views that will serve as a source for the DWH tables


You can download the script and the manual MKST.zip

Update your statistics

If your database suffers of poor performances, before you try any optimization, you should update all statistics. Otherwise SQL Server will produce suboptimal plans.
Out of there a lot of databases have no maintenance plan simply because no one is in charge of it.


This simple query will generete the script for you.

SELECT 'UPDATE STATISTICS [' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + ']'
FROM sys.tables T
WHERE NOT EXISTS
    ( SELECT TOP 1 1
     FROM sys.extended_properties EP
     WHERE EP.major_id = T.object_id
       AND EP.name = 'microsoft_database_tools_support' )
  AND T.is_ms_shipped = 0
ORDER BY 1