Skip to main content

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+ 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 2.0+
For questions and suggestions write to Miken.

Comments

Popular posts from this blog

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…

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…