SQL Script To Get All Company Database Sizes

Microsoft Dynamics GPFollowing on from my recent script to return the functional currency for all companies I hve revisited an old script which gets the size of database and updated it to work in the same way.

This script needs to be run against your system database (called DYNAMICS by default) and it will return the size of the data and log files alongside the total size of the database for all companies.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @SQL NVARCHAR(MAX)

CREATE TABLE #DatabaseSizes(
	INTERID VARCHAR(5)
	=CMPNYNAM VARCHAR(65)
	=DataSize VARCHAR(20)
	=LogSize VARCHAR(20)
	=TotalSize VARCHAR(20)
)

SELECT
	@SQL = STUFF((
			SELECT 
				CHAR(13) + '
				SELECT
					''' + INTERID + '''
					,''' + CMPNYNAM + '''
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN SIZE ELSE 0 END))) * 8192 / 1048576,15,2) + '' MB'')
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN SIZE ELSE 0 END))) * 8192 / 1048576,15,2) + '' MB'')
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,SIZE))) * 8192 / 1048576,15,2) + '' MB'')
				FROM
					' + INTERID + '.dbo.sysfiles'
			FROM
				SY01500
			ORDER BY
				CMPNYNAM
			FOR
				XML PATH(''), TYPE(.value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #DatabaseSizes
	EXEC sys.sp_executesql @SQL
GO

SELECT
	*
FROM
	#DatabaseSizes
GO

DROP TABLE #DatabaseSizes
GO

Find Column In SQL Using INFORMATION_SCHEMA

Microsoft Dynamics GPLast year I posted a script to find tables containing a particular column using sys objects. Steve Endow of Dynamics GP Land suggested using the INFORMATION_SCHEMA instead as he found it easier to use.

I’ve recently had reason to search for tables with a particular column in them, so I took a look at using a script using INFORMATION_SCHEMA.COLUMNS. However, when taking a detailed look at the results I found a few anomalies; the issue was that INFORMATION_SCHEMA.COLUMNS returns results for columns in not only tables, but also views. Which does make sense as both tables and views have columns. For what I was working on I needed a list of only tables.

I did a little exploring of the INFORMATION_SCHEMA and determined that I could join to INFORMATION_SCHEMA.TABLES and filter on TABLE_TYPE <> ‘VIEW’ to get a result set of only tables:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @ColumnToFind VARCHAR(20) = 'PAYRCORD'
SELECT
	['Tables'].TABLE_SCHEMA AS 'Schema'
	,['Tables'].TABLE_NAME AS 'Table'
FROM
	INFORMATION_SCHEMA.COLUMNS AS ['Columms']
INNER JOIN
	INFORMATION_SCHEMA.TABLES AS ['Tables']
		ON
			['Tables'].TABLE_CATALOG = ['Columms'].TABLE_CATALOG
		AND
			['Tables'].TABLE_SCHEMA = ['Columms'].TABLE_SCHEMA
		AND
			['Tables'].TABLE_NAME = ['Columms'].TABLE_NAME
		AND
			['Tables'].TABLE_TYPE <> 'VIEW'
WHERE
	COLUMN_NAME = @ColumnToFind
ORDER BY
	'Schema'
	,'Table'

In the original posts script I was using the sys objects directly, but was filtering out the views by joining to sys.tables which contains only tables. Both the original script and the above one return exactly the same result set.

So, what’s the difference?

INFORMATION_SCHEMA, or System Information Schema Views to give the full name, is one of several methods SQL Server provides to get an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes may have been made to the underlying system tables. The information schema views included in SQL Server originally complied with the ISO standard definition for the INFORMATION_SCHEMA, but appear to have diverged from the standard as new standards have been introduced.

The metadata returned by INFORMATION_SCHEMA, comes from the sys objects. So by using the former you are getting information from the latter, but in a way which should be future proofed against database changes.

SQL Script To Create macro To Activate BOMs

Microsoft Dynamics GPWhile implementing Microsoft Dynamics GP for a new client a while ago, we used Integration Manager to import over 100,000 Inventory items and then a SQL script to insert the 80,000 bill of materials (BOMs).

This worked well, in that it got all of the information loaded, but found that if we inserted the BOMs in this way, they could not be viewed in the BOM Inquiry window.

I used a SQL query to change the status from Active to Pending and then looked at the best way of changing them to Active through the Dynamics GP.

The only way to bulk change the data was by using a GP Macro; the usual way we do this is to do an extract of the data and then mailmerge this into a prerecorded macro in Microsoft Excel.

However, this can be avoided, by using the SQL select to not only get the data, but to output the macro at the same time:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT DISTINCT
	'# DEXVERSION=11.00.0364.000 2 2
	CheckActiveWin dictionary ''default''  form bmBillMaintenance window bmBillMaintenance 
	  TypeTo field ''Item Number'' , ''' + RTRIM(BMH.ITEMNMBR) + '''
	  MoveTo field ''Bill Status'' item 0 
	  ClickHit field ''Bill Status'' item 2  # ''Pending'' 
	  MoveTo field ''Expansion Button 1'' 
	  ClickHit field ''Expansion Button 1'' 
	NewActiveWin dictionary ''default''  form bmBillMaintenance window ChangeStatus 
	  ClickHit field ''Bill Status'' item 1  # ''Active'' 
	  MoveTo field ''Process Button P'' 
	  ClickHit field ''Process Button P'' 
	NewActiveWin dictionary ''default''  form bmBillMaintenance window bmBillMaintenance 
	  MoveTo field ''Save Button'' 
	  ClickHit field ''Save Button'' 
	'
FROM
	BM00101 AS BMH
INNER JOIN
	BM00111 AS BMC
		ON BMC.ITEMNMBR = BMH.ITEMNMBR
WHERE
	BMH.Bill_Status = 2

I needed to make sure that SSMS was configured to return the data into text and that the data returned was more than the default 256 characters.

Once you have the returned macros, save the file, open Bill of Materials Maintenance and then run the macro.

Find All Custom SQL Objects In Database

Microsoft SQL ServerQuite a long time ago I started using a particular naming convention when creating SQL objects such as tables, triggers, or views. The plan was so that they were easy to find in the database.

With some clients who have objects with this naming convention looking to do upgrades I’ve taken the next step and created some SQL queries to select all of these objects (which was always the next step).

The naming convention I adopted is in the following format:

  • type
  • organisation who created
  • client
  • name (which will be omitted if the object is a generic one which might be given to multiple clients)

So, a custom table, created by azurecurve for Fabrikam, Inc. to store a Sales Order/Assembly cross reference would be called ut_AZRCRV_FAB_SalesOrderAssemblyXref.

The type prefix varies by object type, but always starts with a u for user. The types I use are:

  • ut for tables
  • uv for views
  • uf for functions
  • usp for stored procedures
  • utr for triggers

The following view (following my naming convention above lacks a client as it is generic) selects all custom objects in the database created by AZRCRV:

CREATE VIEW uv_AZRCRV_GetCustomObjects AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT o.name,'' AS 'table name', o.type_desc, o.modify_date FROM sys.objects AS o WHERE o.name LIKE 'u__AZRCRV_%'
UNION ALL
SELECT i.name, o.name, o.type_desc, o.modify_date FROM sys.indexes AS i INNER JOIN sys.objects AS o ON o.object_id = i.object_id WHERE I.name LIKE 'u%_AZRCRV_%'
UNION ALL
SELECT t.name, o.name, t.type_desc, o.modify_date FROM sys.triggers AS t INNER JOIN sys.objects AS o ON o.object_id = t.object_id WHERE o.name LIKE 'u%_AZRCRV_%'

The view can either by run manually in SQL Server Management Studio or plugged into either SmartList Designer or SmartList Builder. Once all custom items have been located, they can be extracted and preserved as scripts to be redeployed after the upgrade if necessary.