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 Pass A DataTable To A Stored Procedure Using C# and SQL Server



TODO:

Have you ever wanted to pass a DataTable from your C# application to a Stored Procedure in SQL Server?

 

SOLUTION:

Step 1:  Create the User-Definied Table Type in SQL Server

CREATE TYPE [dbo].[MyUserDefinedType] AS TABLE(
	[Id] [int] NULL,
	[FieldOne] [int] NULL,
	[FieldTwo] [int] NULL
)

 

Step 2:  Create the DataTable and call the Stored Procedure

using (DataTable myDataTable = new DataTable())
{
     myDataTable.Columns.Add(“FieldOne”, System.Type.GetType("System.Int32"));
     myDataTable.Columns.Add(“FieldTwo, System.Type.GetType("System.Int32"));
     myDataTable.Columns.Add(“FieldThree, System.Type.GetType("System.Int32"));
                
     DataRow dataRow = null;
                
     for (int i = 0; i < 100; i++)
     {
          dataRow = myDataTable.NewRow();  
          dataRow[“FieldOne] = 1;
          dataRow[“FieldTwo”] = 2;
          dataRow[“FieldThree] = 3;
          myDataTable.Rows.Add(dataRow);           
     }

     using (SqlConnection connection = new SqlConnection(“connectionstring”))
     {
          
          using (SqlCommand command = connection.CreateCommand())
          {
               connection.Open();
               command.CommandType = CommandType.StoredProcedure;
               command.CommandTimeout = 90;
               command.CommandText = “MyStoredProcedureName;
               command.Parameters.AddWithValue(“@IncomingDataTable”, myDataTable);
               command.ExecuteNonQuery();
          }
     }
}


Step 3:  Create the Table

CREATE TABLE MyTable(FieldOne int NULL, FieldTwo NULL, FieldThree NULL)

 

Step 4:  Create the Stored Procedure

CREATE PROCEDURE [dbo].[MyStoredProcedure]
@MyUserDefinedType MyUserDefinedType READONLY

AS
BEGIN 

    INSERT INTO dbo.MyTable
	([FieldOne], [FieldTwo], [FieldThree])
	
	SELECT [FieldOne], [FieldTwo], [FieldThree] FROM @MyUserDefinedType

END

 

 

NOTES:

Be sure that the DataTable columns are added in the SAME order as they are created in the SQL Server User Type.

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 Fix The Issue When SQL Jobs Are Running But Not Showing History Records



TODO:

Have you experienced the issue that jobs are running, but you do not see any job history?  This can occur if you have a job that runs at say 3AM, and you have a series of other jobs that run every minute or so during the day.  Those jobs will fill up the Job History, and the entry for the job in question is purged from the history, due to the Maximum History setting.

 

SOLUTION:

1.  Right click on SQL Agent, choose Properties.

2.  Click to History

3.  Increase the value for "Maximum Job History Log Size (in rows)

 

NOTES:

There are no notes on this topic.

How To Find Tables With Large Amounts Of Data In SQL Server



TODO:

You want to find tables that contain large amounts of data, which can be due to large row counts.  The solution will get table stats, and sort by row count descending.

 

SOLUTION:

SELECT 
    t.NAME AS 'Table',
    i.name as 'Index',
    sum(p.rows) as 'Rows',
    sum(a.total_pages) as 'Total Pages', 
    sum(a.used_pages) as 'Used Pages', 
    sum(a.data_pages) as 'Data Pages',
    (sum(a.total_pages) * 8) / 1024 as 'Total Space (MB)', 
    (sum(a.used_pages) * 8) / 1024 as 'Used Space (MB)', 
    (sum(a.data_pages) * 8) / 1024 as 'Data Space (MB)'
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    Rows DESC

 

NOTES:

There are no notes on this topic.

How To Print Messages In a SQL Server Stored Procedure



TODO:

Have you ever wanted to print output messages in a stored procedure?

 

SOLUTION:

PRINT 'some string'

PRINT 'some string' + @SomeVariable

 

NOTES:

Note, if you are concatenating a numeric variable you will need to cast it to Varchar

How To Drop A Column From A Table In SQL Server Using T-SQL



TODO:

Have you ever wanted to drop a column to an existing SQL Server Table using T-SQL?

 

SOLUTION:

ALTER TABLE Customer DROP COLUMN FavoriteColor

 

NOTES:

There are no notes on this topic

How To Add A Column To An Existing Table In SQL Server Using T-SQL



TODO:

Have you ever wanted to add a column to an existing SQL Server Table using T-SQL?

 

SOLUTION:

ALTER TABLE Customer ADD FavoriteColor VARCHAR(20)

 

NOTES:

There are no notes on this topic