Script SQL Server
Automatic full backup for all the databases
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'C:\Backup\'
-- specify filename...
SQL 2016 - Migration Performance Improvement
Declare @name nvarchar(100),
@strSQL nvarchar(400)
Declare CUR CURSOR FOR
Select [name] from sysdatabases where name not in ('TempDB')
Open CUR
Fetch Next From CUR
Into @name
While @@FETCH_STATUS = 0
Begin
Set...
Reindex and rebuild all the indexes Online or Off-Line
/*
use DBA
go
exec DBA.spIndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium = 'INDEX_REBUILD_ONLINE',
@FragmentationHigh =...
Register an error in SQL Log
sp_addmessage @msgnum = 50006,
@severity = 16,
@msgtext = N'There are missing Backups',
@replace = 'replace'
GO
Multiple lines in one line with total using XML and CTE
with NameList
as
( Select
FName = 'Leo'
Union All
Select
FName = 'Rodrigo'
),NameListTotal
as
( Select
Total = Count(1)
From NameList
)
Select
FName = (SELECT FName + ' ' AS 'data()' FROM NameList FOR XML PATH('')),
Total
From NameListTotalwith...
SQL Query Analyze Query Shortcuts Commands
This's a trick to create a shortcut using SQL Management Studio for store procedures and usual queries like that:
Sample:
USE master
GO
CREATE PROCEDURE spAnything
AS
print 'SQL Server'
Go to Tools -> Options:
Add the new sp or query command in...
SQL Server Concatenate results on one line
Created with your sample data and you can query as below:
Select [Date], Client_id,
Stuff((Select ','+Item_desc from #alldata where [Date] = a.[Date] and [Client_id] = a.Client_id for xml path('')), 1,1,'')
from #alldata a
group by [Date], Client_id
Input table:
create table #alldata...
SSRS Job Schedule - Subscription
SELECT
C.Name,
[Locale],
[InactiveFlags],
'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Next Run Time' =...
Package distribution SQL Script
Problem
I sometimes get requests to copy a database from one server to another without any data. I just need an empty database with just the schema information and no data, how can I do it?
Solution
There are a few potential solutions to this problem, for example any of the following methods will...