Wednesday, April 26, 2017

Alternative and Effective method of Using sp_msforeachdb

/*Lot of you may not be aware that the command string parameter  does  not allow more than 2000 characters. So what  happens if the parameter you want to pass is greater than 2000.
Below is the script you can use while running across the database activities.Also unlike sp_msforeachdb  the script isn't undocumented.*/


/****** Object:  Stored Procedure [dbo].[run_query_across_databases]    Script Date: 02/20/2017 22:50:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[run_query_across_databases]
@sql_command VARCHAR(MAX)
AS
BEGIN
       SET NOCOUNT ON;
-- Stores database name for use in the cursor
DECLARE @database_name VARCHAR(300)        
DECLARE @sql_command_to_execute NVARCHAR(MAX) -- Will store the TSQL after the database name has been inserted

 -- Stores our final list of databases to iterate through, after filters have been applied
       DECLARE @database_names TABLE          (database_name VARCHAR(100))
       DECLARE @SQL VARCHAR(MAX)-- Will store TSQL used to determine databaselist
       SET @SQL =
       '      SELECT
                     SD.name AS database_name
              FROM sys.databases SD
       '

       -- Prepare database name list
       INSERT INTO @database_names  ( database_name )
       EXEC (@SQL)

       DECLARE db_cursor CURSOR FOR SELECT database_name FROM @database_names
       OPEN db_cursor

       FETCH NEXT FROM db_cursor INTO @database_name

       WHILE @@FETCH_STATUS = 0
       BEGIN
         -- Replace "?" with the database name
         SET @sql_command_to_execute = REPLACE(@sql_command, '?', @database_name)          
  
         EXEC sp_executesql @sql_command_to_execute

         FETCH NEXT FROM db_cursor INTO @database_name
       END

       CLOSE db_cursor;
       DEALLOCATE db_cursor;
END

---------------------------------------------------------------------------------

--Example below showing the list of each tables under each Database on the  current server.

DECLARE @sqlCommand NVARCHAR(MAX)
SET @sqlCommand='
USE [?]
SELECT ''?'',NAME FROM ?.SYS.OBJECTS WHERE TYPE=''U''
'
EXEC dbo.run_query_across_databases @sqlCommand
--------------------------------------------------------------------------------






Friday, October 21, 2016

Easy Way to get the output of the INSERT including the SELECT columns


/*Since the Output clause returns the rows affected by INSERT,UPDATE,DELETE and -When you want the output of the Insert statement
along with the columns from select statement used as source for  Insert, then you take this approach*/




CREATE TABLE #tblEmpTypeTmp (NewEmpTypeID INT,          ExistingEmpTypeID INT)

INSERT INTO MyDB.dbo.tblEmpType (EmpType,  EmpDecodeMethod)
OUTPUT inserted.EmpTypeID,
        cf.EmpTypeID 
        INTO #tblEmpTypeTmp
SELECT cf.EmpType,
        cf.EmpDecodeMethod
  FROM AnotherDB.dbo.tblEmpType AS cf
 

--The above approach errors out saying it cannot identify the column  'cf.EmpTypeID'
--So , take the below approach using Merge
 
CREATE TABLE #tblEmpTypeTmp (NewEmpTypeID INT,
                        ExistingEmpTypeID INT)
 
MERGE INTO MyDB.dbo.tblEmpType
USING AnotherDB.dbo.tblEmpType AS cf
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT(EmpType, EmpDecodeMethod) Values(cf.EmpType, cf.EmpDecodeMethod)
Output inserted.EmpTypeID, cf.EmpTypeID INTO
#tblEmpTypeTmp(NewEmpTypeID, ExistingEmpTypeID);

select * from #tblEmpTypeTmp

Friday, June 6, 2014

Simple way to find who Truncated or Dropped the Table



/*Most Challenging task sometimes would be to figure who dropped the table or who truncated the table on SQL Server database.

So how do we find who performed this operation and when.

Among the Undocumented Functions there are two functions you need to know that might give you some insight.

1.fn_dblog ()

2.fn_dump_dblog ()

The function fn_dblog () works only when transaction log entries have not been purged from the transaction log file (either by CHECKPOINT in Simple recovery model or by the T-Log backup in Full recovery model).

In case the transaction log entries has been purged from the log file because a transaction log backup has been taken after the truncate occurred, we can still use the function fn_dump_dblog() to read the log backup file to get our information.



If you have simple recovery mode on the database, then the Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds. And so if your'e lucky enough you can get the information before it does the checkpoint.

If you have Full Recovery mode,The logs flushed into disk everytime Log Truncates. So either you can recover the information before the log truncates or else your better of by restoring the Full Backup\T Logs in a different environment and then getting the necessary information.*/


--Below is the working example of how it works for Truncate actions:

--Create the table and Insert the Values

CREATE TABLE TestTable
(empid INT identity(1,1), empname VARCHAR(100))
GO

INSERT INTO TestTable
VALUES
( 'David' ),
( 'Nicol' ),
( 'James' ),
( 'Larson' )

GO

/***********************************************************************************************************************/

--Truncate table issued

Truncate TABLE TestTable

DECLARE @ObjectName SYSNAME
DECLARE @TransactionID NVARCHAR(500)


--Find the TransactionID

SET @ObjectName = 'dbo.TestTable'
SET @TransactionID = ( SELECT TOP 1 [Transaction ID] FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName = @ObjectNameORDER BY [Transaction ID] DESC)
 
SELECT @TransactionID


-- Retrieving the UserName & Time when the table was truncated based on the TransactionID
 
SELECT
[Transaction Name], SUSER_SNAME([Transaction SID]) AS UserName
[Begin Time], Operation, [Transaction ID]
FROM
fn_dblog(NULL, NULL)
WHERE [Transaction ID] = @TransactionID -- @TransactionID
AND [Transaction Name] LIKE 'TRUNCATE%'
AND Operation = 'LOP_BEGIN_XACT'

--The (NULL, NULL) is the starting LSN and ending LSN to process NULL means process everything available

-- Output Results below :

/*******************************************************************************************

Transaction Name Username Begin Time Operation TransactionID

------------------------------------------------------------

TRUNCATE TABLE ckadmin\James 2010/06/16 18:23:03:32 LOP_BEGIN_XACT 00000:000000f0

*******************************************************************************************/
--Drop Table Issued

DROP TABLE TestTable

--First we need to find the transactions that drop tables in the log:
SELECT [Transaction Id], [Begin Time], SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N'DROPOBJ';

GO

-- Output Results below :

/*******************************************************************************************

TransactionID Begin Time User

------------------------------------------------------------

0000:000000f0 2010/06/16 18:23:03:32 ckadmin\James


*******************************************************************************************/
--Pass the TraansactionID To get the Objectid of the dropped Table
SELECT TOP (1) [Lock Information] FROM fn_dblog (NULL, NULL)
WHERE [Transaction Id] = <TransactionID>
AND [Lock Information] LIKE '%SCH_M OBJECT%';

-- Output Results below :

/*******************************************************************************************

Lock Information

--------------------------------------

HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 25:245575913:0


*******************************************************************************************


25:245575913 is database_id and the object_id


Since the object has been dropped already you may not be able to validate the table name . So log in to the database which has been previously restored from the backup

and run the below statement against the same database to get the table name.*/
SELECT OBJECT_ID(245575913) as TableName

 /**********************************************************************************************************************/

Monday, March 10, 2014

Simple way to calculate the Max memory Settings for SQL Server

--Run the below script from management Studio and run the output from SP_CONFIGURE statement.
 
DECLARE
@memInMachine DECIMAL(9,2)
@memOsBase DECIMAL(9,2)
@memOs4_16GB DECIMAL(9,2)
@memOsOver_16GB DECIMAL(9,2)
@memOsTot DECIMAL(9,2)
@memForSql DECIMAL(9,2)
SET
@memInMachine = (SELECT physical_memory_in_bytes/(1024*1024*1024) FROM sys.dm_os_sys_info)

SET @memOsBase = 1
-- When memory <=16G
SET @memOs4_16GB =
CASE
WHEN @memInMachine <= 4 THEN 0
WHEN @memInMachine > 4 AND @memInMachine <= 16 THEN (@memInMachine - 4) / 4
WHEN @memInMachine >= 16 THEN 3
END
-- When memory >16G
SET @memOsOver_16GB =
CASE WHEN @memInMachine <= 16 THEN 0
ELSE (@memInMachine - 16) / 8 END
SET
@memOsTot = @memOsBase + @memOs4_16GB + @memOsOver_16GB
SET  @memForSql = @memInMachine - @memOsTot
PRINT 'Memory in machine ' + CAST(@memInMachine AS VARCHAR(10)) + ' GB.'
PRINT 'Memory for OS ' + CAST(@memOsTot AS VARCHAR(10)) + ' GB.'
PRINT 'Memory for SQL Server ' + CAST(@memForSql AS VARCHAR(10)) + ' GB. Note that this assumes one SQL Server instance!'
PRINT ' '
PRINT 'You can copy below to a query window and execute. Make sure the value is reasonable. This assumes that the OS is dedicated to one SQL Server instance!'
PRINT 'EXEC sp_configure ''max server memory'', ' + CAST(CAST(@memForSql * 1024 AS INT) AS VARCHAR(10)) + ' RECONFIGURE'
 

Monday, June 24, 2013

Simple way to Automate Generating SQL Stored Procedure Backup Script.


Requirements:
  • From SQL Server 2005 version onwards
  • Windows Powershell  enabled
Steps:
  • Create a SQL Agent job and paste the below code (See in the attached snapshot)

#PLEASE PUT THE DRIVE NAME YOU WANT TO WRITE TO BELOW EXAMPLE "C:"
$rootDrive = "R:\RDAT8\SQL\SCRIPTS"
#the full path of the file that you want to script the stored procs to
$strDate = (get-Date).tostring("yyyyMMddHHssmm")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
foreach($sqlDatabase in $srv.databases)
{
 $procs = $sqlDatabase.StoredProcedures
 $views = $sqlDatabase.views
 $tables = $sqlDatabase.tables
 $udfs = $sqlDatabase.UserDefinedFunctions
 $sqlDatabaseName = $sqlDatabase.name
 $MyScripter.Server=$srv

  "************* $sqlDatabaseName"

 #STORED PROCEDURES
 if($procs -ne $null)
 {
  foreach ($proc in $procs)
  {
   #Assuming that all non-system stored procs have proper naming convention and don't use prefixes like "sp_"
   if ( $proc.Name.IndexOf("sp_") -eq -1 -and $proc.Name.IndexOf("xp_") -eq -1  -and $proc.Name.IndexOf("dt_") -eq -1)
   {
  
    $fileName = $proc.name
    "Scripting SP $fileName"
    $scriptfile = "$rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\StoredProcedures\$filename.sql"
    New-Item $rootDrive\DatabaseScripts -type directory -force | out-null
    New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName -type directory -force | out-null
    New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate -type directory -force | out-null
    New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\StoredProcedures -type directory -force | out-null
    $MyScripter.Options.FileName = $scriptfile
    #AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end
    $MyScripter.Options.AppendToFile = "true"
    $MyScripter.Script($proc)|out-null
   }
  }
}
}


  • Change the folder location and filter out the system generated stored procedures (Highlited in the snapshot)
  • Schedule it to run according to your need.
  • The script will create a folder called Databasescripts under  the specified folder and will create each database folder under it followed by the datestamp folder.

Benefits:
  • When  the database Backups become huge in size , we don't have space to restore them to  restore the older version of the stored procedures/functions etc. for troubleshooting or rollback purposes.
  • We can have the table schema and stored procedure scripted out for quick references and also versioning it.

 

Thursday, March 7, 2013

Generate DML Script by using TabileDiff Generator For Deployment Purposes

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Prerequisites:
1.Make sure you have the TableDiff utility installed as part of SQL Server Server Installation.
2.Versions supported -SQL 2005,SQL 2008,SQL 2008R2,SQL 2012 64 Bit versions.
If you have installed 32 bit then change the exe path in the code accordingly(C:\Program Files (x86)\...
3.Make sure commandshell option is enabled from the SQL Server .

Description:
1.Performs a table diff on the source and destination data and Generates the DML SQL Script which can be used for Deployment.
2.Only generates the file when it finds the difference and each table is generated in a separate file.Format(<Tablename>_<DATETIMESTAMP>_diff.sql
3.Files are generated in the SQL Server box that the script is running.
3.All the parameters except TableName is mandatory
4.Also you can view the Result in the output window and troubleshoot if there is any error.

EXAMPLE: ExeC usp_TableDiffGenerator 'DEVSERVER','QASERVER','Employee','Employee','dbo',EmployeeDetails,'E:\temp\'
*/

CREATE PROCEDURE [usp_TableDiffGenerator]
@SourceServer VARCHAR(100) , @DesinationServer VARCHAR(100),@SourceDB VARCHAR(100),@DestinationDB
VARCHAR(100),@Schema VARCHAR(50),@Table VARCHAR(50),@Filepath VARCHAR(100)
AS

BEGIN
SET
QUOTED_IDENTIFIER ON
DECLARE @TableNames as table (id int identity(1,1),tableName varchar(100))
DECLARE @file_results TABLE (file_exists int, file_is_a_directory int, parent_directory_exists int )
DECLARE @sTableDiff nvarchar(1000)
DECLARE @tableName varchar(100)
DECLARE @counter int
DECLARE @maxCount int
DECLARE @Time VARCHAR(20)
DECLARE @TableDiffexecutablePath VARCHAR(500)
DECLARE @FileName varchar(255)
DECLARE @FileName1 varchar(255)
DECLARE @Folder varchar(255)
DECLARE @File_Exists INT
DECLARE @folder_exists INT

SET @Time= CONVERT(VARCHAR(10),GETDATE(),120)+ '_'+ REPLACE(CONVERT(VARCHAR(8),GETDATE(),114),':','')

IF COALESCE(@SourceServer,'')='' OR COALESCE(@DesinationServer,'')='' OR COALESCE(@SourceDB,'')='' OR COALESCE(@DestinationDB,'')='' OR COALESCE(@Schema,'')=''
OR COALESCE(@Filepath,'')=''
BEGIN
SELECT 'Parameters are Mandatory'
RETURN
END
--Check if the folder exist
INSERT INTO @file_results (file_exists, file_is_a_directory, parent_directory_exists)
exec master.dbo.xp_fileexist @Filepath
select @folder_exists = file_is_a_directory from @file_results if @folder_exists=0
BEGIN
SELECT
'Folder Does not Exist.'
RETURN
END

--SQL2012
SELECT @FileName1='C:\Program Files\Microsoft SQL Server\110\COM\tablediff.exe'
EXEC Master.dbo.xp_fileexist @FileName1, @File_Exists OUT
SET @TableDiffexecutablePath=@FileName1
IF @File_Exists<>1-- File not Found--SQL2008 AND SQL 2008 R2
BEGIN
SELECT @FileName='C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe'
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
SET @TableDiffexecutablePath=@FileName
IF @File_Exists <>1 -- File not Found--SQL20005
BEGIN
SELECT @FileName='C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe'
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
SET @TableDiffexecutablePath=@FileName
IF @File_Exists <>1 -- File not Found
BEGIN
SELECT 'TableDiff.exe Does not Exist.'
RETURN
END
END
ENDIF @Table IS NOT NULL--Specific Table
BEGIN
INSERT INTo @TableNames SELECT @Table
SET @maxCount=1
END
ELSE
BEGIN
INSERT INTo @TableNames
SELECT so.[name] as table_name
FROM sys.objects so
inner join sys.schemas sc ON so.schema_id = sc.schema_id WHERE type = 'U' and sc.[name] =@Schema

SELECT @maxCount = COUNT(*)
FROM sys.objects so
inner join sys.schemas sc ON so.schema_id = sc.schema_id WHERE type = 'U' and sc.[name] =@Schema
END
--Initialize the Counter
 SET @counter = 1
WHILE @counter <= @maxCount
BEGIN
SELECT @tableName = tableName
FROM @TableNames
WHERE id = @counter

SET @sTableDiff= '""'+ @TableDiffexecutablePath + '" -sourceserver '+ @SourceServer +' -sourcedatabase '+ @SourceDB+ ' -sourceschema '+ @Schema + ' -sourcetable ' + @tableName + ' -destinationserver '+ @DesinationServer+ ' -destinationdatabase ' + @DestinationDB + ' -destinationschema '+ @Schema + ' -destinationtable ' + @Schema +  '.'+ @tableName + ' -f ' + @Filepath + @tableName + '_'+ @Time + '_Diff.sql"'

EXEC XP_CMDSHELL @sTableDiff
Set @counter = @counter + 1
END
END