SQL Server

by

How to split a column in SQL Server?

Today, one of my friend ask me: I have a column that contain different data and contains commas (delimiter character is comma), so could you split it into one specific column.

I want to show with picture what he wants.

He wanted to get second column, it means first data after first comma.

I show you how to transform this data in a test table.

create table ComplexData(
id int,
StringData varchar(250)
);
go

insert into ComplexData(id,StringData) values(1,'10000,Istanbul,20,0,Red')
insert into ComplexData(id,StringData) values(2,'30000,Ankara,30,1')
insert into ComplexData(id,StringData) values(3,'40000,Izmir,20,0,Red')
insert into ComplexData(id,StringData) values(4,'30000,Elazig,30,0')
insert into ComplexData(id,StringData) values(5,'10000,Bursa,20,0,Red')
insert into ComplexData(id,StringData) values(6,'30000,Adana,30,0')
insert into ComplexData(id,StringData) values(7,'40000,Kocaeli,20,1,Red')
insert into ComplexData(id,StringData) values(8,'30000,Ankara,40,1')
insert into ComplexData(id,StringData) values(9,'40000,Kocaeli,20,1,Red')
insert into ComplexData(id,StringData) values(10,'30000,Eskisehir,40,1,Blue')

After test data I should create a udf function.

-- =============================================
-- Author: Selcuk KILINC
-- Create date: 2021.10.08
-- Description: String Split
-- =============================================
CREATE or ALTER FUNCTION UDF_StringSplit
(
@stringParam varchar(250),
@splitChar varchar(2),
@whichSubStr int
)
RETURNS varchar(100)
AS
BEGIN
declare @tbl table(
id int identity(1,1),
SubStr varchar(100)
);

insert into @tbl(SubStr)
select value from string_split(@stringParam,@splitChar)

-- Return the result of the function
RETURN (select SubStr from @tbl where id=@whichSubStr)

END
GO

Then call the udf in the query:

select *,dbo.UDF_StringSplit(StringData,',',2) as City from ComplexData;

With the 3rd parameter you can split whatever you want. I tried to get second data from string above.

by

How to drop/delete/truncate all tables in a specific database?

First we need a test database and test tables


--- Create a test db
create database DUMMYDB;
go
---- create tables in test db
use DUMMYDB;
go
create table t1(id int);
go
create table t2(id int);
go
create table t3(id int);
go

if there is no foreign keys, you can execute one of follow queries (drop, delete or truncate)


use DUMMYDB;
go
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' " --- drop all tables in current db
exec sp_MSforeachtable "DELETE FROM ? PRINT '? deleted' " --- delete all tables in current db
exec sp_MSforeachtable "TRUNCATE TABLE ? PRINT '? truncated' " --- truncate all tables in current db

by

Basic Daily Monitoring Queries

use master;
go
— How to get instance name?

select @@SERVERNAME

— How to get SQL Server version?

select @@version

— How to show the full space percentage of the log files?

dbcc sqlperf(logspace)

— How to show free space on disks?

exec xp_fixeddrives

— How to get last restarted date of SQL Server?

select sqlserver_start_time from sys.dm_os_sys_info

— How to check databases size in SQL Server?

exec sp_helpdb

exec sp_helpdb 'AdventureWorks2014' ---> it returns details for spesific database

exec sp_databases --- alternative

— How to get blocking queries?

select * from sys.sysprocesses where blocked<>0

— List databases with state

select name,state_desc from sys.databases

— How to get all my databases last full, diff, tran backup info

select db_name,backup_type,backup_size_in_MB,
backup_start_date,backup_finish_date,is_copy_only,
physical_device_name,recovery_model_desc
from (
select d.database_id,
d.name as db_name,
case backup_types.Backup_Type
when 'D' then 'Full Backup'
when 'I' then 'Differential Backup'
when 'L' then 'Transaction Log Backup'
end as 'backup_type',
cast(bs.compressed_backup_size/1048576 as decimal(15,2)) as 'backup_size_in_MB',
bs.backup_start_date,
bs.backup_finish_date,
bs.is_copy_only,
bmf.physical_device_name,
d.recovery_model_desc,
row_number() over(partition by d.database_id,backup_types.Backup_Type order by bs.backup_start_date desc) as rwn
from sys.databases as d
cross join(
select 'D' as Backup_Type
union
select 'I'
union
select 'L'
) as backup_types
left join msdb.dbo.backupset as bs on (d.name=bs.database_name and backup_types.Backup_Type=bs.type)
left join msdb.dbo.backupmediafamily as bmf on bs.media_set_id=bmf.media_set_id
where isnull(bs.is_copy_only,0)=0 --- if you want to filter copy_only backups
) as tbl
where tbl.rwn=1
and tbl.database_id>4 --- eliminate system databases
order by tbl.database_id asc

— How to get all indexes on a spesific table in SQL Server?

-- exec sp_helpindex 'Table_Name'
exec sp_helpindex 'HumanResources.Department'

— How to list all indexes with last statistics updated date

SELECT OBJECT_NAME(object_id) as table_name,
name AS index_name,
STATS_DATE(object_id, index_id) AS StatsUpdated
FROM sys.indexes
order by StatsUpdated desc

by

Last Backup Dates

Following script lists last full, diff and transaction log backup dates and some useful details of all databases in an instance.

select db_name,backup_type,backup_size_in_MB,
backup_start_date,backup_finish_date,is_copy_only,
physical_device_name,recovery_model_desc
from (
select d.database_id,
d.name as db_name,
case backup_types.Backup_Type
when 'D' then 'Full Backup'
when 'I' then 'Differential Backup'
when 'L' then 'Transaction Log Backup'
end as 'backup_type',
cast(bs.compressed_backup_size/1048576 as decimal(15,2)) as 'backup_size_in_MB',
bs.backup_start_date,
bs.backup_finish_date,
bs.is_copy_only,
bmf.physical_device_name,
d.recovery_model_desc,
row_number() over(partition by d.database_id,backup_types.Backup_Type order by bs.backup_start_date desc) as rwn
from sys.databases as d
cross join(
select 'D' as Backup_Type
union
select 'I'
union
select 'L'
) as backup_types
left join msdb.dbo.backupset as bs on (d.name=bs.database_name and backup_types.Backup_Type=bs.type)
left join msdb.dbo.backupmediafamily as bmf on bs.media_set_id=bmf.media_set_id
where isnull(bs.is_copy_only,0)=0 --- if you want to filter copy_only backups
) as tbl
where tbl.rwn=1
and tbl.database_id>4 --- eliminate system databases
order by tbl.database_id asc

If you want to filter system databases and copy_only backups, mind the comments in the script.

You can notice the below result set some columns contain NULL. Does that mean there are no backups have been performed? May be. Please ensure that backup and restore history cleanup script has not been run recently in msdb database. Maybe later I will explain why do we need cleaning backup/restore history tables and how to clean up?