The stored procedures provides the following parameters:
@command1, @command2, @command3: the main commands to be executed (at least the first command must be executed)
@precommand: a command to be executed before the @command1,
@postcommand: a command to be executed after all the commands were executed successfully,
@replacechar: the character used to represent the table within the commands, by default a question mark (?) character
@whereand: used to limit the scope only to a set of tables
Before the introduction of Data Management Views with SQL Server 2005, the stored procedure was used to get the number of records for all tables:
-- getting the number of records for each table EXEC sp_MSForEachTable @command1='SELECT ''?'' [Table], COUNT(*) NoRecords FROM ?'
To restrict running the command(s) only for a table, the object_id function can be used within the @whereand parameter:
-- getting the number of records for a table EXEC sp_MSforeachtable @command1='SELECT ''?'' [Table], COUNT(*) NoRecords FROM ?' -- , @whereand = ' And Object_id = Object_id(''[Person].[Address]'')'
In AX 2009 and even the later versions including Dynamics 365 (as long the direct access to the database is possible) the number of records could be restricted to a given company (aka business unit):
-- getting the number of records from a mandant sp_MSforEachTable @command1 = 'SELECT ''?'' [Table], COUNT(*) NoRecords FROM WHERE DataAreaId IN (''DAT'')'
The procedude can be used to perform CRUD operayions on each table. During a data migration it was possible to clean out the business units not needed with just a simple script:
--delete the data for multiple data areas sp_MSforEachTable @command1 = 'DELETE FROM ? WHERE DataAreaId IN (''m01'')'
Administrations tasks can be performed as well, e.g. updating statistics, performing consistency checks for each table, etc.:
-- for each table EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''' -- displaying the disk space researved/used EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL' -- updating statistics EXEC sp_MSforeachtable 'DBCC CHECKTABLE (''?'') WITH ALL_ERRORMSGS, NO_INFOMSGS' -- check table considerncy
A set of tasks can be performed for a single table by providing the constraint within the @whereand parameter:
-- executing multiple commands for a table EXEC sp_MSforeachtable @command1='UPDATE statistics ? WITH ALL' -- updating statistics , @command2= 'DBCC CHECKTABLE (''?'') WITH ALL_ERRORMSGS, NO_INFOMSGS' -- check table considerncy , @whereand = ' And Object_Name(Object_id) = ''[Person].[Address]'''
Executing the commands against a subset of tables involves using a persisted or temporary table to store the name of the tables which shoul be considered, eventually with further metadata to allow filtering:
-- dropping the table -- DROP TABLE IF EXISTS #Tables -- create a temporary table SELECT TableName INTO #Tables FROM (VALUES ('[Person].[Address]') , ('[Person].[AddressType]') , ('[Person].[BusinessEntity]')) DAT(TableName) -- getting the number of records for the list of tables EXEC sp_MSForEachTable @command1='SELECT ''?'' [Table], COUNT(*) NoRecords FROM ?' , @whereand = ' And Object_id In (Select Object_id(TableName) FROM #Tables)'
Warnings:
Do not forget to dump the temporary table when finished!
The code is provided only for exemplification purposes. You can use the above code on your own risk!
Undocumented features can be deprecated in future versions of SQL Server, therefore they should be used with precaution in long-term solutions.
Happy coding!:
Do not forget to dump the temporary table when finished!
The code is provided only for exemplification purposes. You can use the above code on your own risk!
Undocumented features can be deprecated in future versions of SQL Server, therefore they should be used with precaution in long-term solutions.
Happy coding!:
No comments:
Post a Comment