How To Create IsWeekday Function In T-SQL To Determine If A Date Is A Weekday Or Weekend



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

How To Delete The Last X Records From A Table By Group



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

How To Change The Encryption Password On A Database Master Key



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.

How To Restore A SQL Server Database Master Key On A New Server



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.

How To List All Columns In All User Tables In A Sql Server Database



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

How To Update From Using T-SQL



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.

How To Use a CASE Statement In T-SQL



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.

How To Incrementally Shrink A SQL Server Database Using T-SQL



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

How To Find The Tables And Columns Referenced By A Foreign Key Using T-SQL



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.

How To Create a Comma Separated List Of Table Columns Using T-SQL



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.