Query all CRM databases for empty Transaction Currency fields

 

Sometimes it’s really handy to loop all CRM DBs in search of the value of some field for all organizations. This query results in all DBs + Tables + Columns + Number of empty Transaction Currency Ids

Use this at will for any analysis.

 

SET NOCOUNT ON
GO
use master
GO

DECLARE
@dbsql nvarchar(max),
@dbname nvarchar(500),
@dbid uniqueidentifier,
@variable2 int,
@variable3 int,
@totaldb int = 0,
@totaldbonserver int = 0,
@totaldbwithmatches int = 0

-- Get CRM databases
DECLARE DBCursor CURSOR for SELECT DatabaseName, Id FROM MSCRM_CONFIG.dbo.Organization

OPEN DBCursor
fetch next from DBCursor into @dbname, @dbid

while (@@FETCH_STATUS <> -1)
    BEGIN       
        DECLARE @ParmDefinition NVARCHAR(500)
        DECLARE @columncountquery nvarchar(500) = 'select @variable2OUT = count(*) from dorcastest_mscrm.INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = ''TransactionCurrencyId'''

        SET @ParmDefinition = N'@variable2OUT int OUTPUT'
        set @totaldbonserver = @totaldbonserver + 1

        EXEC sp_executesql @columncountquery, @ParmDefinition, @variable2 OUTPUT
            print 'Number of TransactionCurrencyId-columns in ' + @dbname + ': ' + CONVERT(varchar, @variable2)

       if @variable2 > 0
        BEGIN
        declare
                  @columncursor     as cursor,
                  @columnsql        as nvarchar(max),
                  @columnquery      as nvarchar(max),
                  @table_catalog    as nvarchar(255),
                  @table_schema     as nvarchar(255),
                  @table_name       as nvarchar(255),
                  @column_name      as nvarchar(255),
                  @columncount      as int = 0

                  set @columnquery = 'select ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG,
                  ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA,
                  ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.TABLE_NAME,
                  ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
                  from ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS, ' + @dbname +  '.INFORMATION_SCHEMA.TABLES
                  where ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.table_catalog = ' + @dbname +  '.INFORMATION_SCHEMA.TABLES.table_catalog
                  and ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.table_schema= ' + @dbname +  '.INFORMATION_SCHEMA.TABLES.table_schema
                  and ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.table_name = ' + @dbname +  '.INFORMATION_SCHEMA.TABLES.table_name
                  and ' + @dbname +  '.INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = ''TransactionCurrencyId''
                  and ' + @dbname +  '.INFORMATION_SCHEMA.TABLES.TABLE_TYPE = ''BASE TABLE'''

                  set @columnsql = 'set @cursor = cursor forward_only static for ' + @columnquery + ' open @cursor;'

                  exec sys.sp_executesql
                        @columnsql
                        ,N'@cursor cursor output'
                        ,@columncursor output

                  fetch next from @columncursor into @table_catalog, @table_schema, @table_name, @column_name
                  while (@@fetch_status = 0)
                  begin
                      declare @currencyidsql nvarchar(max) = 'select @variable3OUT = count(*) from '+ @table_catalog + '.' + @table_schema + '.' + @table_name + ' where transactioncurrencyid is null'
                      declare @Parm3Definition NVARCHAR(500) = N'@variable3OUT int OUTPUT'

                    EXEC sp_executesql @currencyidsql, @Parm3Definition, @variable3 OUTPUT
                    if @variable3 > 0
                              begin      
                                   print  @table_catalog + '.' + @table_schema + '.' + @table_name + '.' + @column_name + ' : ' + CONVERT(varchar, @variable3)
                              end

                        fetch next from @columncursor into @table_catalog, @table_schema, @table_name, @column_name
                  end

                  close @columncursor
                  deallocate @columncursor
        END   

        fetch next from DBCursor into @dbname, @dbid

    END

    /*
    PRINT 'Total CRM databases on server: '
    PRINT @totaldbonserver
    PRINT 'Total CRM databases tested () : '
    Print @totaldb
    PRINT 'Total CRM databases with matches: '
    Print @totaldbwithmatches
    */

CLOSE DBCursor
DEALLOCATE DBCursor

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s