Skip to main content

Posts

SqlDbAid DBA tool

Latest version (v2.6.0.0 - 2017-09-03) 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+ supportText search inside code definition with highlitghtingDBA 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 scriptsTable data insert script creationTable/View data exportOffline database compare tool
Requirements .Net Framework 2.0+ For questions and suggestions write to Miken.
Recent posts

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 WHERENOT( EXISTS (SELECT1FROM 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 O…

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.

SETTRANSACTIONisolationlevelREADuncommitted

SELECTCASE
WHENER.total_elapsed_time>0THEN
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_…

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, ...).


SELECTCASEWHEN P.class IN(0,3)THEN P.class_desc ELSE O.type_desc END type_desc,CASEWHEN P.class IN(0,3)THEN''ELSE SCHEMA_NAME(O.schema_id)END object_schema,CASEWHEN P.class =3THEN SCHEMA_NAME(P.major_id)WHEN P.class =1THEN 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+' '+CASEWHEN P.class =0THEN P.permission_name +' TO '+ QUOTENAME(USER_NAME(P.grantee_principal_id)) COLLATE database_default WHEN P.class =3THEN P.permission_name +' ON SCHEMA::'+ QUOTE…

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 sto…

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+']'FROMsys.tablesTWHERENOTEXISTS(SELECTTOP11FROMsys.extended_propertiesEPWHEREEP.major_id=T.object_idANDEP.name='microsoft_database_tools_support')ANDT.is_ms_shipped=0ORDERBY1