Archive

Archive for the ‘SQL Server’ Category

Đổi tên Database trong SQL Server

1. Yêu cầu :

Đổi tên Database trong SQL SQL Server

2. Vấn đề : 

Khi thực hiện đổi tên 1 db, có thể bạn sẽ gặp lỗi sau : 

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

Lỗi này thường xảy ra khi db của bạn ở chế độ MULTI_USER và có user đang truy cập hoặc có đối tượng nào đó đang tham chiếu đến nó. 

3. Cách giải quyết : 

Trước tiên thiết đặt db về chế độ SINGLE_USER, sau đó đổi tên db, cuối cùng đặt lại nó về chế độ MULTI_USER.

( Chế độ SINGLE_USER sẽ quy định tại một thời điểm chỉ một user có thể truy cập db và thường được sử dụng cho thao tác maintenance. Nếu user khác kết nối đến db tại thời điểm bạn thiết đặt database về chế độ SINGLE_USER thì các kết nối đó sẽ bị đóng mà không bị cảnh báo gì. )

3.1 Đổi tên db sử dụng SQL Server Management Studio 

* Thiết đặt db về chế độ SINGLE_USER : 

– Trong cửa sổ Object Explorer, click chuột phải vào db cần thiết đặt, chọn Properties

– Trong cửa sổ Database Properties, chọn mục Options trong phần Select a page

– Tìm đến option Retrict Access, chọn giá trị SINGLE_USER, sau đó ấn nút OK

set_single_user

– Nếu có user khác đang kết nối đến db thì sẽ xuất hiện hộp thoại Open Connections. Để đồng ý thay đổi chế độ và đóng các kết nối khác, ấn Yes

open_connections

* Đổi tên db 

– Trong cửa sổ Object Explorer, click chuột phải vào db cần thiết đặt, chọn Rename 

– Gõ tên mới cho db, ấn Enter

* Thiết đặt db về lại chế độ MULTI_USER : 

– Thực hiện thao tác tương tự như với việc thiết đặt chế độ SINGLE_USER, đặt giá trị Restrict AccessMULTI_USER

3.2 Đổi tên db sử dụng Transact-SQL 

Ví dụ bạn cần đổi tên db Test sang Test_New

-- Set the database to SINGLE_USER mode
ALTER DATABASE Test
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
-- Try to rename the database
ALTER DATABASE Test MODIFY NAME = Test_New
GO
-- Set the database to MULTI_USER mode
ALTER DATABASE Test_New
SET MULTI_USER WITH ROLLBACK IMMEDIATE
Categories: SQL Server

Loại bỏ dấu tiếng Việt trong T-SQL

Cách 1: Loại bỏ dấu tiếng Việt, giữ nguyên chữ in hoa và các ký tự đặc biệt

CREATE FUNCTION [dbo].[fuConvertToUnsign1]
(
 @strInput NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN 
 IF @strInput IS NULL RETURN @strInput
 IF @strInput = '' RETURN @strInput
 DECLARE @RT NVARCHAR(4000)
 DECLARE @SIGN_CHARS NCHAR(136)
 DECLARE @UNSIGN_CHARS NCHAR (136)
 SET @SIGN_CHARS = N'ăâđêôơưàảãạáằẳẵặắầẩẫậấèẻẽẹéềểễệế
 ìỉĩịíòỏõọóồổỗộốờởỡợớùủũụúừửữựứỳỷỹỵý
 ĂÂĐÊÔƠƯÀẢÃẠÁẰẲẴẶẮẦẨẪẬẤÈẺẼẸÉỀỂỄỆẾÌỈĨỊÍ
 ÒỎÕỌÓỒỔỖỘỐỜỞỠỢỚÙỦŨỤÚỪỬỮỰỨỲỶỸỴÝ'
 +NCHAR(272)+ NCHAR(208)
 SET @UNSIGN_CHARS = N'aadeoouaaaaaaaaaaaaaaaeeeeeeeeee
 iiiiiooooooooooooooouuuuuuuuuuyyyyy
 AADEOOUAAAAAAAAAAAAAAAEEEEEEEEEEIIIII
 OOOOOOOOOOOOOOOUUUUUUUUUUYYYYYDD'
 DECLARE @COUNTER int
 DECLARE @COUNTER1 int
 SET @COUNTER = 1
 WHILE (@COUNTER <=LEN(@strInput))
 BEGIN 
 SET @COUNTER1 = 1
 WHILE (@COUNTER1 <=LEN(@SIGN_CHARS)+1)
 BEGIN
 IF UNICODE(SUBSTRING(@SIGN_CHARS, @COUNTER1,1))
 = UNICODE(SUBSTRING(@strInput,@COUNTER ,1) )
 BEGIN 
 IF @COUNTER=1
 SET @strInput = SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1)
 + SUBSTRING(@strInput, @COUNTER+1,LEN(@strInput)-1) 
 ELSE
 SET @strInput = SUBSTRING(@strInput, 1, @COUNTER-1)
 +SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1)
 + SUBSTRING(@strInput, @COUNTER+1,LEN(@strInput)- @COUNTER)
 BREAK
 END
 SET @COUNTER1 = @COUNTER1 +1
 END
 SET @COUNTER = @COUNTER +1
 END
 SET @strInput = replace(@strInput,' ','-')
 RETURN @strInput
END

Ví dụ :

SELECT [Test].[dbo].[fuConvertToUnsign1] (N'Hà Nội mùa thu, cây cơm nguội vàng!!!')

–> Kết quả :

Ha-Noi-mua-thu,-cay-com-nguoi-vang!!!

 

Cách 2 : Loại bỏ dấu tiếng Việt, chuyển in hoa thành in thường, loại bỏ các ký tự đặc biệt

CREATE FUNCTION [dbo].[fuConvertToUnsign2]
(
 @strInput NVARCHAR(4000)
) 
RETURNS NVARCHAR(4000)
AS
Begin
 Set @strInput=rtrim(ltrim(lower(@strInput)))
 IF @strInput IS NULL RETURN @strInput
 IF @strInput = '' RETURN @strInput
 Declare @text nvarchar(50), @i int
 Set @text='-''`~!@#$%^&*()?><:|}{,./\"''='';–'
 Select @i= PATINDEX('%['+@text+']%',@strInput ) 
 while @i > 0
 begin
 set @strInput = replace(@strInput, substring(@strInput, @i, 1), '')
 set @i = patindex('%['+@text+']%', @strInput)
 End
 Set @strInput =replace(@strInput,' ',' ')
 
 DECLARE @RT NVARCHAR(4000)
 DECLARE @SIGN_CHARS NCHAR(136)
 DECLARE @UNSIGN_CHARS NCHAR (136)
 SET @SIGN_CHARS = N'ăâđêôơưàảãạáằẳẵặắầẩẫậấèẻẽẹéềểễệế
 ìỉĩịíòỏõọóồổỗộốờởỡợớùủũụúừửữựứỳỷỹỵý'
 +NCHAR(272)+ NCHAR(208)
 SET @UNSIGN_CHARS = N'aadeoouaaaaaaaaaaaaaaaeeeeeeeeee
 iiiiiooooooooooooooouuuuuuuuuuyyyyy'
 DECLARE @COUNTER int
 DECLARE @COUNTER1 int
 SET @COUNTER = 1
 WHILE (@COUNTER <=LEN(@strInput))
 BEGIN 
 SET @COUNTER1 = 1
 WHILE (@COUNTER1 <=LEN(@SIGN_CHARS)+1)
 BEGIN
 IF UNICODE(SUBSTRING(@SIGN_CHARS, @COUNTER1,1)) 
 = UNICODE(SUBSTRING(@strInput,@COUNTER ,1) )
 BEGIN 
 IF @COUNTER=1
 SET @strInput = SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1) 
 + SUBSTRING(@strInput, @COUNTER+1,LEN(@strInput)-1) 
 ELSE
 SET @strInput = SUBSTRING(@strInput, 1, @COUNTER-1) 
 +SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1) 
 + SUBSTRING(@strInput, @COUNTER+1,LEN(@strInput)- @COUNTER)
 BREAK
 END
 SET @COUNTER1 = @COUNTER1 +1
 END
 SET @COUNTER = @COUNTER +1
 End
 SET @strInput = replace(@strInput,' ','-')
 RETURN lower(@strInput)
End

Ví dụ :

SELECT [Test].[dbo].[fuConvertToUnsign2] (N'Hà Nội mùa thu, cây cơm nguội vàng!!!')

–> Kết quả :

ha-noi-mua-thu-cay-com-nguoi-vang

 

 

 

Categories: SQL Server

Xóa tất cả Procedures từ 1 database trong SQL Server

1. Xóa store procedures

Create Procedure dbo.DeleteAllProcedures
As
 declare @procName varchar(500)
 declare cur cursor
 for select [name] from sys.objects where type = 'p'
 open cur
 
 fetch next from cur into @procName
 while @@fetch_status = 0
 begin
 if @procName <> 'DeleteAllProcedures'
 exec('drop procedure ' + @procName)
 fetch next from cur into @procName
 end
 close cur
 deallocate cur
Go
 Grant Execute On dbo.DeleteAllProcedures To Public
Go

2. Xóa store procedures + views + functions

USE [db_name]
 
CREATE PROCEDURE dbo.__DeleteAllProcedures
As
declare @procName varchar(500)
 
-- Removes stored procedures
declare cur cursor
for select [name] from sys.objects where [type] = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
if @procName <> '__DeleteAllProcedures'
exec('drop procedure ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
 
-- Removes Views
declare cur cursor
for select [name] from sys.objects where [type] = 'v'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop view ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
 
-- Removes Functions
declare cur cursor
for select [name] from sys.objects WHERE [type] = 'fn'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop function ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
 
-- removes itselfs
DROP PROCEDURE __DeleteAllProcedures
 
Go
 
exec
__DeleteAllProcedures

 

Categories: SQL Server

Xóa nhiều bảng trong database SQL Server

1. Đối với SQL Server 2005 

DECLARE @id varchar(255) 
DECLARE @dropCommand varchar(255)
DECLARE tableCursor CURSOR FOR 
 SELECT name FROM sys.tables WHERE name <> 'CacheManager'  -- ( điều kiện của bảng muốn xóa )
OPEN tableCursor 
FETCH next FROM tableCursor INTO @id
WHILE @@fetch_status=0 
BEGIN 
 SET @dropcommand = N'drop table ' + @id 
 EXECUTE(@dropcommand) 
 FETCH next FROM tableCursor INTO @id 
END
CLOSE tableCursor 
DEALLOCATE tableCursor

2. Đối với SQL Server 2000

DECLARE @id1 varchar(255),@dropCommand varchar(255)
DECLARE tableCursor CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE='BASE TABLE' and table_name like 'GPN%'
OPEN tableCursor FETCH next FROM tableCursor INTO @id1 WHILE @@fetch_status=0
BEGIN
print 'drop table ' +@id1
FETCH next FROM tableCursor INTO @id1
END
CLOSE tableCursor
DEALLOCATE tableCursor
Categories: SQL Server

Truy vấn SQL lấy record bất kỳ thứ i

-- Khai báo 1 bảng tạm (Cần lấy các trường nào thì khai báo thêm vào)
DECLARE @Import TABLE (rownum INT IDENTITY (1, 1) PRIMARY KEY NOT NULL , columnName columnType)
-- Insert dữ liệu từ bảng thật vào bảng tạm
INSERT INTO @Import (columnName) SELECT columnName FROM tableName
-- Lấy bản ghi thứ bất kỳ
SELECT columnaName FROM @Import WHERE rownum = rowNumber
-- Nếu lấy bản ghi đầu tiên --> rowNumber = 1
SELECT columnaName FROM @Import WHERE rownum = 1
-- Lấy bản ghi cuối cùng
DECLARE @lastRow INT
SELECT @lastRow = COUNT(*) FROM USER_DB
SELECT NOMBRE FROM @Import WHERE rownum = @lastRow
Categories: SQL Server

Truy vấn SQL lấy record từ thứ i đến thứ j (SQL Server 2005)

USE DatabaseName;
GO
WITH OrderedOrders AS
(
 SELECT *,
 ROW_NUMBER() OVER (ORDER BY ColumnName ) AS 'RowNumber'
 FROM TableName 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN i AND j;
Categories: SQL Server

Insert nhiều bản ghi cùng lúc sử dụng một câu lệnh insert

Thông thường, để chèn n bản ghi vào một bảng, bạn cần viết n câu lệnh insert kiểu như sau :

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
 VALUES ('First',1);
INSERT INTO MyTable (FirstCol, SecondCol)
 VALUES ('Second',2);
INSERT INTO MyTable (FirstCol, SecondCol)
 VALUES ('Third',3);
INSERT INTO MyTable (FirstCol, SecondCol)
 VALUES ('Fourth',4);
INSERT INTO MyTable (FirstCol, SecondCol)
 VALUES ('Fifth',5);
GO

Theo cách này, mệnh đề INSERT INTO lặp lại rất nhiều lần, thật là nhàm chán và hẳn là bạn sẽ copy, paste nó để tiết kiệm thời gian. Giải pháp cho bạn ở đây là :

1. Sử dụng mệnh đề UNION ALL và INSERT INTO … SELECT …

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

2. Sử dụng Row Constructor ( SQL Server – 2008 )

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
Categories: SQL Server