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...

We have this great Website about the Stored Procedure : sp_whoisactive - Version 11.30 - December 10, 2017 This version does NOT create a stored procedure, it just run in an ad hoc manner. 1 2-- http://whoisactive.com 3 4SET QUOTED_IDENTIFIER ON; 5SET ANSI_PADDING ON; 6SET CONCAT_NULL_YIELDS_NULL ON; 7SET ANSI_WARNINGS...

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...

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...

Context: Database log file is huge and you are not able to shrink it. You do not have more free space. Disaster is coming, or it is already there. What you have done? I hope you never use this script, but if you need to use this script, you must to think how you never use again. This atomic database bomb. Shrink...

When we have un have an unhealthy data synchronization state, we can always use graphic interface to resume data movement. This script resume when the database need it, but you must connect on secondary server to run it. 1 2-- You can use SQLCMD Mode 3-- :CONNECT <MyServer> 4PRINT '-- SQL Script to SET HADR RESUME'...

When you have several sql jobs that need to be disabled all. If you can, you can just turn off SQL Agent but, sometime, it is not possible. This script generates the script to enable and disable. 1 2--generate disable 3SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs 4...

We have a lot of blogs and documents that can explain how service broker works. https://msdn.microsoft.com/en-us/library/bb839489(v=sql.105).aspx This step by step how to configure SQL Server Service Broker between two servers. You have two Windows Servers with SQL Server each one with a database. We will use this list...

Create mirror between two server without witness (High safety without automatic failover - synchronous). This exemple use TechNet Virtual Labs : Exploring AlwaysOn Availability Groups in SQL Server 2014 This script will not change anything in your database, it just will create the scripts for. All script was create for...

You can manually disable or enable the service through SQL Server Management Studio or run the ALTER QUEUE command for: 1 2-- Enable 3ALTER QUEUE [queuename] WITH STATUS = ON; 4 5-- Disable 6ALTER QUEUE [queuename] WITH STATUS = OFF; But, it can be complicated when you need over all database. This script enable all...

1</p> 2 3<p>echo off 4cls 5set token=0 6tasklist /FI "imagename eq cmd.exe" /NH /FO csv &gt; tk.lst 7type tk.lst 8echo.---------------- 9for /F "eol=; tokens=2,3* delims=," %%i in (tk.lst) do call :process %%i 10goto :eof</p> 11 12<p>:process</p> 13 14<p>if "%token%"=="0" ( 15 set token=1 16 goto :eof 17) 18 19echo...

Check SQL Version 1 2SELECT 3@@servername AS ServerName 4, CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '8%' THEN 'SQL2000' 5WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '9%' THEN 'SQL2005' 6WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) LIKE '10.0%' THEN...

Before to start to change everything, you must have a baseline that it established in normal load operation for comparisons. 1 2SELECT TOP 20 3 qs.sql_handle 4, DB_NAME(CAST(pa.value as INT)) as DatabaseName 5, qs.execution_count 6, qs.total_worker_time AS Total_CPU 7, total_CPU_inSeconds = qs.total_worker_time/1000000...

A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped. This code can be used to drop Login from SQL Server and user name associated with this Login in different databases. 1 2------------------------------------ 3-- Drop user -...

Enable broker service on all databases but it check if Service Broker is enabled. 1 2-- ***************************************** -- 3-- SQL Script to ENABLE_BROKER 4-- ***************************************** -- 5-- 6PRINT @@servername; 7PRINT Getdate(); 8BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH; 9...

We have many ways to drop a database, but if you want to drop many databases. You can have some problems with opened connections, but this script kill all connection and drop the database. 1 2-- ***************************************** -- 3-- SQL Script to drop databases 4-- *****************************************...

Unused indexes should be deleted, but you would not drop all the unused indexes without deep analyse. This a script delete all unused indexes. - Nonclustered indexes Non primary keys Non unique Non unused (#Total reads < 100) Finding Unused Indexes – Only Nonclustered 1 2SELECT 3 db_name() as DatabaseName 4...

A config file for a .NET application is a text file that has a name of myapplication.exe.config. The Windows CE does not make things easy for you! It allows you to add a file named “App.config” to your project and it will copy it to the appropriate bin directory and rename it to myapplication.exe.config, but you cannot...

This script backup and shrink all database. Everybody says to never use it but shrinking is necessary if your log/data has grown out of control, or as part of a process to remove excessive fragmentation. Source: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/...