02/08/2024
Below is a collection of useful tSQL scripts that can be used to perform standard tasks.
ALTER TABLE dbo.YourTable
ADD ID INT IDENTITY(1,1)
select
DATEDIFF(hour,DOB2,GETDATE())/8766.0 AS AgeYearsDecimal
,CONVERT(int,ROUND(DATEDIFF(hour,DOB2,GETDATE())/8766.0,0)) AS AgeYearsIntRound
,DATEDIFF(hour,DOB2,GETDATE())/8766 AS AgeYearsIntTrunc
from table
EXEC sp_rename '[tablename].fieldname', 'newfieldname', 'COLUMN';
ALTER TABLE tbl_MAIN_LOG
ALTER COLUMN f23 NVARCHAR(50)
USE DatabaseName;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:SQLServerBackupsAdventureWorks2012.Bak'
WITH FORMAT,
MEDIANAME = 'Z_SQLServerBackups',
NAME = 'Full Backup of AdventureWorks2012';
GO
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
CREATE FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
DECLARE @Result varchar(2000)
SET @Str = coalesce(@Str,'')
SET @Str = LOWER(@Str) + ' '
SET @Result = ''
WHILE 1=1
BEGIN
IF PATINDEX('% %',@Str) = 0 BREAK
SET @Result = @Result + UPPER(Left(@Str,1))+
SubString (@Str,2,CharIndex(' ',@Str)-1)
SET @Str = SubString(@Str,
CharIndex(' ',@Str)+1,Len(@Str))
END
SET @Result = Left(@Result,Len(@Result))
RETURN @Result
END
SELECT * INTO BizDev.CurrCustomers FROM Sales.Customers
SELECT * INTO BizDev.CurrCustomers FROM Sales.Customers where 0=1
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
SELECT TOP 10 s.session_id,
r.status,
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
USE [master];
GO
CREATE LOGIN MyNewAdminUser
WITH PASSWORD = N'abcd',
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
GO
EXEC sp_addsrvrolemember
@loginame = N'MyNewAdminUser',
@rolename = N'sysadmin';
-- firstly - lets stick our first query into a CTE (common table expression ) this is a temp area where
-- we can query the assigned reference once.. in this case its called G.1
with G as(
select BoilerUIN ,Name, rn from
(
SELECT
ROW_NUMBER() OVER (PARTITION BY BoilerUIN ORDER BY Stamp) AS rn
, BoilerUIN
, Name
FROM SPF.dbo.tbl_Results
where LEFT (TestRig, 5) = 'WS201'
) as D
where rn = 1
)
-- end CTE
-- now we can innerjoin the previous query.
SELECT a.BoilerUIN, G.Name
FROM SPF.dbo.tbl_Results A
inner join G on a.BoilerUIn = G.boilerUIN
where Stamp > '2013-06-01'
and TestRig IN ('rignameWS211', 'rignameWS212')
GROUP BY a.BoilerUIN, g.name
HAVING SUM (case when TestName = 'Finished' and Pass = 'P' THEN 1 ELSE 0 END) > 0
and SUM (case when TestName = 'Finished' and Pass = 'F' THEN 1 ELSE 0 END) = 0
with fs
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB,
((select sum(size) from fs where type = 0 and fs.database_id = db.database_id) + (select sum(size) from fs where type = 1 and fs.database_id = db.database_id)) TotalMB
from sys.databases db
-- ========================================
-- List of variables - just set the date year at top
-- ========================================
declare @ViewYear int = 2021; -- year(getdate());
declare @tb2 table(dt datetime,dayname nvarchar(50),wkNo int,wkNoMonth int);
declare @dayno int = 0
declare @dateA datetime = 0
-- ========================================
-- dynamically build a year
-- ========================================
while (@dayno < 366)
begin
set @dateA = dateadd(DAY, @dayno, cast(concat('01/jan/' , @ViewYear) as datetime))
insert into @tb2 (dt, dayname, wkNo, wkNoMonth)
select @dateA dt
, (FORMAT(@dateA, 'ddd')) dayName
, (DATEPART(wk, @dateA)) wkNo
, (DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dateA), 0), @dateA) +1) wkNoMonth
set @dayno = @dayno + 1;
end;
-- ========================================
-- get our relevant foramts
-- ========================================
select
replace(left(format(dt,'yyyy-MM-dd'),10),'-','') as dateID,
dt as DateValue,
year(dt) as yearNumber,
Month(dt) as MonthNumber,
replace(left(format(dt,'yyyy-MM-dd'),8),'-','') as YearMonth,
concat(left(format(dt,'MMMM'),3),'-',year(dt)) as MonthYearName,
DAY(dt) dayNumber,
DATEPART(weekday,dt) [dayOfweek],
DATENAME(dw,dt) daysName,
format(dt,'MMMM') as MnthName,
wkNoMonth as MonthWeekNumber,
case wkNoMonth when 1 then 'First' when 2 then 'Second' when 3 then 'Third' when 4 then 'Forth' when 5 then 'Fifth' when 6 then 'Sixth' end as MonthWeekName,
wkNo WeekNumberOfYear
from @tb2
select * from (
select appid,
ROW_NUMBER() OVER (PARTITION BY appid ORDER BY appid) AS rn
from tablENAME
) d
where rn > 1
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
UPDATE
table1
SET
table1.ProductidS = B.ProductIDs
FROM
table1 A
INNER JOIN
table2 B
ON
A.LGCodes = B.LGCodes
where
A.LGCodes = B.LGCodes
UPDATE
table1
SET
table1.ProductidS = B.ProductIDs
FROM
table1 A
INNER JOIN
table2 B
ON
A.LGCodes = B.LGCodes
where
A.LGCodes = B.LGCodes
use master
go
SELECT login_time as [SQL Server Instance Uptime] FROM sys.sysprocesses where spid=1;
use master
-- connect to the server
EXEC sp_addlinkedserver
@server=N'servernameweb',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'servernameweb';
go
-- add login details
exec sp_addlinkedsrvlogin
@rmtsrvname ='servernameweb',
@locallogin = NULL ,
@useself ='false',
@rmtuser = 'username',
@rmtpassword = 'pwd'
go
SELECT * FROM sys.servers
select * from [servernameweb].databasename.dbo.tableName
exec sp_dropserver
@server='servernameweb'
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Total Pages';
;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
SELECT *
FROM
(SELECT CallCentreUserName,
statuscode,
(select sum(amount) as amt from TBL_CASES where statuscode='DIP' and CreatedDate>getdate()-60 and CallCentreUserName=A.CallCentreUserName ) as AMT
FROM TBL_CASES A
where CreatedDate > getdate()-60
) as p
PIVOT
(
count (statuscode)
FOR statuscode IN
(TUD
,WTD
,IND
,QUO
,DIP
,APS
,APP
,SURV
,BRF
,COMP
,NEW
,LPD
,OWS
,ASD
,RES
,TIS
,DNS
,INV
,FIC
,SVY
,RSK
,DNA
,ACC)
) AS pvt
ORDER BY CallCentreUserName;
DECLARE @char CHAR = ''
DECLARE @charI INT = 0
DECLARE @password VARCHAR(100) = ''
DECLARE @len INT = 8 -- Length of Password
WHILE @len > 0
BEGIN
SET @charI = ROUND(RAND()*100,0)
SET @char = CHAR(@charI)
IF @charI > 48 AND @charI < 122
BEGIN
SET @password += @char
SET @len = @len - 1
END
END
SELECT @password [PassWord]
-- query to read an excel file
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=E:ICDataDropextract25.06.2015.xlsx; HDR=YES; IMEX=1',
'SELECT * FROM [TAB1$]')
GO
-- query to build stored procedure to import excel file.
USE [ISGICDataload]
GO
/****** Object: StoredProcedure [dbo].[spx_ImportFromExcel] Script Date: 07/15/2015 16:26:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spx_ImportFromExcel]
@SheetName varchar(20),
@FilePath varchar(100),
@HDR varchar(3)
AS
BEGIN
DECLARE @SQL nvarchar(1000)
DECLARE @SQLRM nvarchar(1000)
declare @TableName varchar(50)
SET @TableName = 'tbl_IMPORTEDDATA'
IF OBJECT_ID (@TableName,'U') IS NOT NULL
BEGIN
SET @SQLRM = 'DROP TABLE ' + @TableName
EXEC sp_executesql @SQLRM
END
SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'
SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='
SET @SQL = @SQL + @HDR + ''''''')...['
SET @SQL = @SQL + @SheetName + ']'
EXEC sp_executesql @SQL
END
USE ejis;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE EJIS
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ejis_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE EJIS
SET RECOVERY FULL;
GO
RESTORE DATABASE NewDatabase
FROM DISK = 'D: BackupFilesTestDatabaseFullBackup.bak'
WITH
MOVE 'PreviousDatabase' TO 'D:ackupDataFilesTestDatabase.mdf',
MOVE 'PreviousDatabase_log' TO 'D:ackupDataFilesTestDatabase_Log.ldf',
NORECOVERY
--RECOVERY:
--STANDBY
-- now restore the transaction logs.
RESTORE LOG NewDatabase
FROM DISK = 'D: BackupFilesTestDatabase_TransactionLogBackup1.trn'
WITH NORECOVERY
RESTORE LOG NewDatabase
FROM DISK = 'D: BackupFiles TestDatabase_TransactionLogBackup2.trn'
WITH NORECOVERY
RESTORE LOG NewDatabase
FROM DISK = 'D: BackupFiles TestDatabase_TransactionLogBackup3.trn'
WITH NORECOVERY
RESTORE LOG NewDatabase
FROM DISK = 'D: BackupFiles TestDatabase_TransactionLogBackup4.trn'
WITH RECOVERY
-- or to specific time.
RESTORE LOG NewDatabase
FROM DISK = ''D: BackupFiles TestDatabase_TransactionLogBackup4.trn'
WITH STOPAT = N'6/28/2007 4:01:45 PM', RECOVERY
EXEC sp_resetstatus 'ides';
ALTER DATABASE ides SET EMERGENCY
DBCC checkdb('ides')
ALTER DATABASE ides SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('ides', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE ides SET MULTI_USER
-- restore a database to a point in time from TRN log backups.
RESTORE DATABASE NewDatabase
FROM DISK = 'D: BackupFilesTestDatabaseFullBackup.bak'
WITH
MOVE 'PreviousDatabase' TO 'D:DataFiles TestDatabase.mdf',
MOVE 'PreviousDatabase_log' TO 'D:DataFiles TestDatabase_Log.ldf',
NORECOVERY
--NORECOVERY: Tells SQL Server that you are not finished restoring the database and that subsequent restore files will occur. While
--the database is in this state, the database is not yet available, so no connections are allowed.
--•RECOVERY: Tells SQL Server that you are finished restoring the database, and it is ready to be used. This is the default option,
--and it is by far the one that is used most often.
--•STANDBY: Tells SQL Server that the current database is not yet ready to be fully recovered and that subsequent log files can be
--applied to the restore. You can use this option so that connections are available to the restore database if necessary. However,
--future transaction logs can only be applied to the database if no current connections exist.
RESTORE LOG NewDatabase
FROM DISK = 'D: BackupFilesTestDatabase_TransactionLogBackup1.trn'
WITH NORECOVERY
RESTORE LOG NewDatabase
FROM DISK = 'D: BackupFiles TestDatabase_TransactionLogBackup2.trn'
WITH NORECOVERY
RESTORE LOG NewDatabase
FROM DISK = 'D: BackupFiles TestDatabase_TransactionLogBackup3.trn'
WITH NORECOVERY
--now the important one - restore the last trn to the point in time your restoring to.
RESTORE LOG NewDatabase
FROM DISK = 'D: BackupFiles TestDatabase_TransactionLogBackup4.trn'
WITH STOPAT = N'6/28/2007 4:01:45 PM', RECOVERY
DECLARE @FN AS VARBINARY(MAX)
SELECT @FN = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK
'C: empwebfontfontuserguide.pdf',
SINGLE_BLOB ) AS x
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemFile)
SELECT NEWID(), 'MS1002','PDF', @FN
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')