TODO:
Have you ever wanted a function that tells you if a certain date is a Weekday?
SOLUTION:
CREATE FUNCTION IsWeekday(@InputDate datetime) RETURNS int
AS
BEGIN
DECLARE @isweekday int
select @isweekday=choose(datepart(dw, @InputDate), 0,1,1,1,1,1,0)
RETURN @isweekday
END
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to keep the last X records for a Customer, Employee, Order, etc?
SOLUTION:
DELETE c FROM (
SELECT *, rn=row_number() OVER (PARTITION BY CustomerId ORDER BY Id desc)
FROM CustomerHistory
) c
WHERE rn > 3
NOTES:
The SQL above will keep the last 3 records in the table CustomerHistory. The Grouping is by CustomerId, and Id is an Identity column, which ensures we keep the last 3 records (order by desc). If you want to test it, just execute the statement as a select (Change DELETE c to SELECT CustomerId, Id, rn)
TODO:
Have you ever wanted to change the password associated with your Database Master Key?
SOLUTION:
use my database
GO
--step 1, open the key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'my password'
GO
--step 2, regenerate it with new password
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD 'new password'
GO
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to backup your database master key and move it to a new server?
SOLUTION:
--Step 1 -- Backup file
OPEN MASTER KEY DECRYPTION BY PASSWORD 'my password'
BACKUP MASTER KEY TO FILE = 'c:\myfile' ENCRYPTION BY PASSWORD ='mypassword'
--Step 2 -- Restore file
USE myDatabase
GO
RESTORE MASTER KEY FROM FILE 'my file'
DECRYPTION BY PASSWORD = 'mypassword'
ENCRYPTION BY PASSWORD = 'mypassword'
FORCE;
GO
NOTES:
You may not need the 'FORCE' option, read up on what it does and decide accordingly.
TODO:
Have you ever wanted to list all columns in all User tables that exist in a SQL Server Database?
SOLUTION:
SELECT so.name "table", sc.name "column", sm.text "Default"
FROM dbo.sysobjects so INNER JOIN dbo.syscolumns sc ON so.id = sc.id
LEFT JOIN dbo.syscomments sm ON sc.cdefault = sm.id
WHERE so.xtype = 'U'
ORDER BY so.[name], sc.colid
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to perform a join during an Update using T-SQL?
SOLUTION:
UPDATE
Person
SET
Person.Name = PersonArchive.Name
FROM
Person
INNER JOIN
PersonArchive
ON
Person.Id = PersonArchive.Id
NOTES:
You can also add a WHERE clause as to not update all records.
TODO:
Have you ever wanted to use a CASE statement in your T-SQL Select Query?
SOLUTION:
SELECT Column =
CASE
WHEN SomeValue = 0 THEN 'Zero'
WHEN SomeValue < 0 THEN 'Minus'
ELSE 'Plus'
END
FROM MyTable
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to shrink a large SQL Server database in small increments?
SOLUTION:
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int
-- Set Name of Database file to shrink
set @DBFileName = 'MyDB'
-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000
-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 100
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Loop until file at desired size
while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin
set @sql =
'dbcc shrinkfile ( '+@DBFileName+', '+
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '
print 'Start ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
exec ( @sql )
if @@ERROR <> 0
begin
print @@ERROR
break
end
print 'Done ' + @sql
print 'at '+convert(varchar(30),getdate(),121)
-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName
end
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to find the table and column names referenced in a Foreign Key Constraint using T-SQL?
SOLUTION:
SELECT
OBJECT_NAME(fk.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS fk
INNER JOIN
sys.foreign_key_columns AS fkc
ON fk.OBJECT_ID = fkc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fkc.referenced_object_id
WHERE
OBJECT_NAME (fk.referenced_object_id) = 'YourTableGoesHere'
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to get a comma separated list of columns that belong to a table using T-Sql?
SOLUTION:
DECLARE @columnList varchar(8000)
DECLARE @name varchar(100)
DECLARE db_cursor CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY COLUMN_NAME ASC;
SET @columnList=''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @columnList=''
SET @columnList = '[' + @name + ']'
ELSE
SET @columnList = @columnList + ',[' + @name + ']'
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT @columnList
NOTES:
Just paste to a query window, hit ctrl-a and F5 to run it.