This article explains how to enable Change Data Capture to trigger/track Tables changes https://www.sqlshack.com/change-data-capture-for-auditing-sql-server/
Category: SQL Server
SQL SERVER – Find where specific table or view is used in SQL Server database
select schema_name(o.schema_id) + '.' + o.name as [table], 'is used by' as ref, schema_name(ref_o.schema_id) + '.' + ref_o.name as [object], ref_o.type_desc as object_type from sys.objects o join sys.sql_expression_dependencies dep on o.object_id = dep.referenced_id join sys.objects ref_o on dep.referencing_id = ref_o.object_id where o.type in ('V', 'U') and schema_name(o.schema_id) = 'dbo' -- put schema name here and … Continue reading SQL SERVER – Find where specific table or view is used in SQL Server database
SQL SERVER – Auto Generate INSERT Statements For A Table In SQL Server
To generate insert scripts for inserting data to a table on production environment or populating a test environment database follow this procedure In SQL Server Management Studio (SSMS), there is an option to easily auto generate insert statements on the fly. Below are the steps to generate insert statements using SSMS. In SSMS Object Explorer, right-click the database. … Continue reading SQL SERVER – Auto Generate INSERT Statements For A Table In SQL Server
SQL SERVER – How to increase the maximum number job history logs
By default the maximum number of logs stored in the SQL SERVER Agent history is 100 All the previous jobs stored after that value are automatically deleted If you need to store more logs in your history follow these instructions : In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand … Continue reading SQL SERVER – How to increase the maximum number job history logs
SQL SERVER – HOW TO : TEMPDB won’t shrink even if there is unused space available
It could happen that the TEMPDB grows and even if you try to shrink it the size does not decrease. The problem could be caused by cache data not being cleared. In this cases you could use some commands that clear these cached data. Read this article to understand what you can do in these … Continue reading SQL SERVER – HOW TO : TEMPDB won’t shrink even if there is unused space available
SQL SERVER – Create an Alert for TEMPDB Growing out of control
The system database TEMPDB can appear to have a life of its own. The primary purpose of this database is to temporarily store data for specific processes before being committed to a database. When a Tempdb grows out-of-control it can be a daunting task trying to figure out what is causing the growth. This can … Continue reading SQL SERVER – Create an Alert for TEMPDB Growing out of control
sql server – Free disk SPACE USING SHRINK
Understanding shrinking the log If you need to recover disk space from the transaction log file, consider shrinking the log file. Shrinking logs helps after you perform an action that creates a large number of logs. You can only shrink the log if there is free space on the log file. Shrink the transaction log … Continue reading sql server – Free disk SPACE USING SHRINK
AX / D365FO – Create index with “Included columns”
If you want to create an Index with Included columns just create the index, add the field and set the "Included column" property to YES
SQL SERVER – Concatenates rows fields into a single string with STRING_AGG()
The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator. It does not add the separator at the end of the result string. This example uses the STRING_AGG() function to generate lists of emails of customers by the city: SELECT city, STRING_AGG(email,';') email_list FROM sales.customers GROUP BY city; This is the … Continue reading SQL SERVER – Concatenates rows fields into a single string with STRING_AGG()
SQL SERVER – DROP TABLE IF EXISTS
SQL Server 2016 introduced a simply way of dropping a table with DROP IF EXISTS. Instead of having to look and see if whether or not the table exists with one T-SQL statement then running DROP TABLE if it does, and ignored if not, executing DROP TABLE IF EXISTS will do both for you in … Continue reading SQL SERVER – DROP TABLE IF EXISTS
AX / Dynamics 365 For Finance and Operations blog 