Indexing is the fast way to be faster The problem is, though … how do you choose the right indexes? How do you choose the right index keys? It’s very easy to choose the wrong indexes, in this case bad performance. FILLFACTOR and PAD_INDEX FILLFACTOR and PAD_INDEX are only used when the index is being built or rebuilt....

Find all CLRs on all databases can be hard work, this code help to find these CLRs. Select all CLRs on a database: 1-- Select all CLRs on a database 2SELECT distinct 3 DB_NAME() AS DatabaseName, 4 udf.type AS Type, 5 udf.name AS Name, 6 udf.object_id AS ID, 7 udf.create_date AS CreateDate, 8 udf.modify_date AS...

SQL Script to export table data in xml files, or export schema data to xsd files This use OLE Automation Stored Procedures (Transact-SQL), and maybe, you need to change the global configuration settings of SQL Server. It can be an issue in production environment, and this case. The SQL Script have three stored...

This query returns list of tables and their columns with details. 1 2SELECT @@Servername AS ServerName 3 , DB_NAME() AS DatabaseName 4 , DB_ID() AS DatabaseId 5 , sc.name AS SchemaName 6 , tab.schema_id AS SchemaId 7 , tab.NAME AS TableName 8 , tab.object_id AS TableId 9 , col.NAME AS ColumnName 10 , col.column_id 11 ,...

Disable all triggers on a table, you do what you have to do, after enable only the triggers that have been disabled. Step 1 : Disable all triggers 1 2--#region Disable triggers 3 4DECLARE @triggers TABLE (SqlEnableTrigger NVARCHAR(MAX)); 5DECLARE @SchemaName VARCHAR(128) = 'mySchema'; – ** TO DO ** 6DECLARE @TableName...

A jobSql Job was changed. The questions are: When? by Who? We suppose you always have a copy of yours jobs. ** Check the SQL Script for shared folder, your e-mail and SMTP server. Delete old audit configuration – make sure you want to do that. 1 2print @@servername 3 4USE [master] 5 6GO 7 8BEGIN TRY ALTER SERVER AUDIT...

Get Job name when you are executing job. This is useful when you need to make reference to the job being executed. 1 2DECLARE @jobname sysname, @jobid uniqueidentifier 3 4SELECT @jobname=b.name,@jobid=b.job_id 5FROM sys.dm_exec_sessions a,msdb.dbo.sysjobs b 6WHERE a.session_id=@@spid 7AND...

you are already in trouble and it is too late when … The transaction log becomes full, SQL Server Database Engine issues a 9002 error. The SQL Server Database Filegroup is Full. Run out of disk space You must have the answer to theses questions: Who many times the database base can still growth? Do we use the best...

Query lists all table (and view) constraints - primary keys, unique key constraints and indexes, foreign keys and check, default constraints, and isDisabled. Columns# SchemaName Name - table or view schema and name object_type - object type: Table View constraint_type - type of constraint: Primary key Unique key...

This Stored procedure send a file by FTP. Check if SQL Agent has access to the file and FTP server. The little trick is to create a file that will run. It can have conflicts easily because filename is always the same. Next version will be with random name. Use this code carefully, it is not 100% safe. 1 2USE master; 3...

Step by step how you can change ‘root’ password You must have rights to stop/start services, and kill process. Step 1: Stop MySql service run: services.msc … Step 2 : Create file to reset password You can use your password and your file name. We use password : Pass1Word2 and file name : C:\temp\reset.txt 1 2USE mysql;...

This code create a table to log all connections. It is very simple, it just logs when, who, from where and which database. For this we use “master.dbo.sysprocesses” https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-2017 Create table to log...

When we write a query than is running for every database in the instance of SQL Server, we use Sp_msforeachdb. If your query is bigger than 2000 chars, the query cannot work because Sp_msforeachdb has a character limitation of 2000 chars. The solution is to re-create MySp_myforeachdb. We use sp_helptext to see the...

This script remedy index fragmentation by reorganizing or rebuilding an index. Small tables, small indexes, low fragmentation, we do not care, and keep simple & easy We are talking about the guidance which is: •if a table has less than 10000 rows, to do nothing •if an index has less than 1000 pages, to do nothing •if...

This script* search for a specific value that you can select sql type: String - %my string% Number - exact number Date - exact format yyyy/MM/dd HH:mm:ss XML - %my string% You can limit how many result by tables using “TOP n” You must select database to run this script, and avoid using production environment. * Tested...

This is quick way to get a string containing the sql datatype required for a column declaration, a create table, a print variables, or convert data to XML and XML to data, based on temp table and column information. How to use: 1. Create your select statement 2. Define if you use or not prefix - you have prefix to...

SSMS default maximum number of characters displayed in each column is 256. You can change this option on: Option/Query Results/SQL Server/Results to Text but, the maximum value is 8192 characters. This is a solution when you need to print more that 8192 characters: 1use master; 2go 3drop proc if exists sp_Print; 4go...

Maintain database indexes You can just create script or run right now the reindex. Verify the configuration to filter databases or to change the FILLFACTOR. 1 2PRINT '-- ***************************************** --' 3PRINT '-- Reindex all tables' 4PRINT '-- on selected databases' 5PRINT '--...

Create backup operator login 1 2PRINT '-- Create backup operator login' 3 4DECLARE @loginName as NVARCHAR(256) = N'UserDbBackupOperator' 5DECLARE @password as nvarchar(128) = N'MyDbBackupStrongPassword' 6 7DECLARE @sql AS NVARCHAR(MAX) 8If not Exists (select loginname from master.dbo.syslogins where name = @loginName )...

Script to get Linked server list. We have repeated linked server name if it has more than one associated remote login. 1 2SELECT @@SERVERNAME AS 'Server Name' 3, sys.servers.server_id AS 'IdLinkedServer' 4, sys.servers.name AS 'Linked Server Name' 5, CASE sys.servers.Server_id WHEN 0 THEN 'Current Server' 6 ELSE...