SQL Server : Multiple scripts execution script

Following script will help to execute multiple scripts and save output too. We can save our time of manual execution if we need to do hundreads of scripts

/*
USAGE:
Provide source folder name where SQL scripts have been placed in
1--> SET @Source='E:\Zap\'
Provide server and database name
SET @srvname = 'Lab1'
SET @dbname = 'Test1'
*/
---------------------------------------------------------------------------------------
set nocount on
DECLARE @Source VARCHAR(500)
DECLARE @SQLPath nvarchar(500)
SET @Source='E:\Zaps\'
SET @SQLPath='dir /b "'+@Source+'*.sql"'
CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(500))
INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell @SQLPath

DECLARE cFiles CURSOR LOCAL FOR
    SELECT DISTINCT [SQLFileName]
    FROM ##SQLFiles
    WHERE [SQLFileName] IS NOT NULL AND
          [SQLFileName] != 'NULL'
    ORDER BY [SQLFileName]

DECLARE @vFileName            VARCHAR(500)
DECLARE @vFileName_op            VARCHAR(500)
DECLARE @vSQLStmt             VARCHAR(1100)
DECLARE @srvname VARCHAR(50)
DECLARE @dbname VARCHAR(50)
DECLARE @err_count int
DECLARE @msg VARCHAR(500)

DECLARE @dest VARCHAR(500)
DECLARE @cmdpath nvarchar(500)

SET @dest=@Source+'Output\'
SET @cmdpath = 'Mkdir "'+@Source+'output"'
SET @srvname = 'Lab1'
SET @dbname = 'Test1'



PRINT 'Running SQL scripts placed in folder: '+@Source
PRINT 'Running SQL scripts on server: '+@srvname
PRINT 'Running SQL scripts on Database: '+@dbname



-----------Check/Create output folder in root folder----------
CREATE TABLE ##xp_fileexist_output (
[FILE_EXISTS] int not null,
[FILE_IS_DIRECTORY] int not null,
[PARENT_DIRECTORY_EXISTS] int not null)

insert into ##xp_fileexist_output
exec master.dbo.xp_fileexist @dest

if exists ( select * from ##xp_fileexist_output where FILE_IS_DIRECTORY = 1 )
begin
print 'Output folder already exist at: '+@dest
end
else
begin
exec master.dbo.xp_cmdshell @cmdpath
print 'Output folder created: '+@dest
end
--------------------------------------------------------------


PRINT 'Starting the execution of SQL scripts!!!'

OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @vFileName_op=REPLACE('OP_'+@vFileName,'.sql','.txt')
    SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S' +@srvname+ ' -d ' +@dbname+ ' -E -n -i "'+@Source+'' + @vFileName + '" -o "'+@dest+''+@vFileName_op+'"'''

SET @msg='Running '+@vFileName+' now!!!'
Print @msg

      PRINT (@vSQLStmt)
    EXECUTE (@vSQLStmt)

-----------Search output file for errors----------
Select @err_count=COUNT(1) from
master..udfReadfileAsTable(@dest,@vFileName_op)
where line like '%msg%'
and line like '%Level%'
and line like '%State%'
------------------------------------------------------
If @err_count>0
BEGIN
 select @msg= '!!! ERROR OCURRED !!! in script name: '+ @vFileName+'
 Output saved in : '+@dest+@vFileName_op
 print @msg
GOTO Abort_label
END
ELSE
BEGIN
select @msg= 'Successfully executed script name: '+ @vFileName +'
Output saved in : '+@dest+@vFileName_op
 print @msg
END
   FETCH NEXT FROM cFiles INTO @vFileName
END
GOTO Skip_label
Abort_label:
BEGIN
PRINT 'Aborted the execution of scripts. Check/Remove the errors and remove the scripts already executed from SQL folder'
select * from forcedfailure
END
Skip_label:

CLOSE cFiles
DEALLOCATE cFiles
GO
------------------Cleanup------------------------
PRINT 'Dropping temp tables!!!'
DROP TABLE ##SQLFiles
DROP TABLE ##xp_fileexist_output
GO

SQL Server : Query for Scripting out multiple procs

use Myproctest_DB
go
Begin
Set nocount on
/***********************************************/
/*Mention proc names In first Select Statement*/
/*Mention folder Path where you want to place the proc scripts in the variable @DirPath = 'e:\temp\ProcMove\Original'*/

/*Possible future enhancements */
/***************************************/
/* 1:- Quoted Identifier on */ -- Done (20 Aug 2019)
/* 2:- Replacement Strings */
/* 3:- Object Permissions */ --  Done (20 Aug 2019)
/* Version 2 - 20 aug 2019 */
/************************************************/

--Cleaning up temp tables
If (Select OBJECT_ID('tempdb..#dir')) is not null Drop table #dir
If (Select OBJECT_ID('tempdb..##ProcDef')) is not null GOTO CleanUp
Print 'Table already exists Pleae double check if someone is using it'--drop table ##ProcDef
If (Select OBJECT_ID('tempdb..#TmpProcDef')) is not null drop table #TmpProcDef
If (Select OBJECT_ID('tempdb..#tmp2')) is not null drop table #tmp2

select state_desc+' '+b.permission_name+' on ['+d.name+'].'+c.name+' to ['+a.name+']' COLLATE DATABASE_DEFAULT
from sys.database_principals a join
sys.database_permissions b on a.principal_id=b.grantee_principal_id join
sys.objects c on b.major_id=c.object_id join
sys.schemas d on c.schema_id=d.schema_id



Declare @DirPath varchar(2000), @DirCmd Varchar(2000)
Set @DirPath = 'e:\temp\ProcMove\Original'

Create table #tmp2 (sno int identity(1,1),srvName sysname, dbName sysname ,xtype sysname, Pname sysname)

Insert into #tmp2 (srvName,dbName,xtype,Pname)
Select @@Servername,db_name(),type_desc,Quotename(OBJECT_SCHEMA_NAME(object_id),'[') + '.' + QUOTENAME(so.name,'[') as Pname from sys.objects so
where so.name in ('phil_response_summary_report')

Select * from #tmp2



-- Checking and Creating the Directory
Set @DirCmd = 'Dir ' + @DirPath

create table #dir (outputs varchar(4000))
insert into #dir
Exec master..xp_cmdshell @DirCmd

IF EXISTS (SELECT * FROM #dir WHERE outputs LIKE '%' + @DirPath + '%')
Begin
Print 'Directory Already Exists'
End
Else
Begin
Print 'Directory Does not exists creating ' + @DirPath
Select @DirCmd = 'MD ' + @DirPath
Exec Master..xp_cmdshell @DirCmd
End
-- Checking and Creating the Directory Ends Here--------------------------



Create table ##ProcDef
(
PSno int identity(1,1),
Pcode Varchar(4000)
)


Create table #TmpProcDef
(
PSno int identity(1,1),
Pcode Varchar(4000)
)


Truncate table ##ProcDef

Declare @sno int, @i int
Select @sno= max(sno) from #tmp2
--Select @sno = 2
--Print @sno
Set @i = 1

While (@i<= @sno )
Begin

Truncate table ##ProcDef
Truncate table #TmpProcDef
Declare @srvName sysname, @dbName sysname, @Xtype sysname, @Pname sysname
Declare @drop_def varchar(50)
Declare @lbrk Char(5), @Tab char(5)
Select @lbrk = Char(13) + Char(10)
Select @tab = Char(9)
--Select ascii(' ')
--Select top 10 * from #tmp2

Select  @srvName = srvName,@dbName =dbName, @xtype = Xtype, @Pname = pname from #tmp2 where sno = @i
If  (@xtype = 'SQL_STORED_PROCEDURE')
  Select @drop_def = 'Drop Procedure '
IF @xtype = 'VIEW'
Select @drop_def = 'Drop View '
IF (@xtype = 'SQL_SCALAR_FUNCTION' OR @xtype = 'SQL_TABLE_VALUED_FUNCTION' OR @xtype = 'SQL_INLINE_TABLE_VALUED_FUNCTION')
Select @drop_def = 'Drop Function '


Declare @drop_proc1 varchar(1000),@drop_proc2 varchar(1000),@drop_proc3 varchar(1000),@drop_proc4 varchar(1000),@drop_proc5 varchar(1000)
Declare @drop_proc6 varchar(1000), @drop_Proc7 Varchar(1000)
Declare @verify_proc1 varchar(1000),@verify_proc2 varchar(1000),@verify_proc3 varchar(1000),@verify_proc4 varchar(1000),@verify_proc5 varchar(1000)
Declare @verify_proc6 varchar(1000),@verify_proc7 varchar(1000),@verify_proc8 varchar(1000),@verify_proc9 varchar(1000)
Select @drop_proc1 = 'IF OBJECT_ID(N'''+@Pname+''') IS NOT NULL'
Select @drop_proc2 = @tab + 'BEGIN ' + @lbrk + @tab + @drop_def + @Pname
Select @drop_proc3 = @tab + 'IF OBJECT_ID(N'''+ @Pname +''') IS NOT NULL'
Select @drop_proc4 = @tab + 'PRINT N''<<< ****FAILED DROPPING PROCEDURE '+ @Pname +' >>>'''
Select @drop_Proc5 = @tab + ' ELSE '
Select @drop_Proc6 = @tab + ' PRINT N''<<< DROPPED PROCEDURE '+ @Pname + ' >>>'' ' + @lbrk +'END'+@lbrk + 'Go'
--Select @drop_Proc7 = '/*************************************************/'



Insert into ##ProcDef Values (@drop_proc1)
Insert into ##ProcDef Values (@drop_proc2)
Insert into ##ProcDef Values (@drop_proc3)
Insert into ##ProcDef Values (@drop_proc4)
Insert into ##ProcDef Values (@drop_proc5)
Insert into ##ProcDef Values (@drop_proc6)
--Insert into ##ProcDef Values (@drop_proc7)
Insert into ##ProcDef Values ('   ')

If  (SELECT uses_quoted_identifier FROM sys.sql_modules  WHERE object_id =object_id(@Pname)) = 1
Begin
Select @drop_Proc7 = 'SET QUOTED_IDENTIFIER ON '+@lbrk + 'GO ' + @lbrk
Print @drop_Proc7
Insert into ##ProcDef Values (@drop_proc7)
Insert into ##ProcDef Values ('   ')
End


--Print @drop_proc1 + @drop_proc2 + @drop_proc3 + @drop_proc4 + @drop_proc5 + @drop_proc6 + @drop_proc7

--Get Proc_definition
Declare @get_ProcDef varchar(1000)
SET @get_procdef = (SELECT '['+@srvName +'].'+ @dbName +'.dbo.sp_helptext_spade '''+ @Pname + '''')
--Print @get_procdef


Truncate table #TmpProcDef
INSERT INTO #TmpProcDef
EXECUTE (@get_procdef)


--please do not modify this statement, this formatting has a special purpose
update #TmpProcDef   
set Pcode = replace(Pcode,'
','')
--set Pcode = replace(Pcode,@lbrk,'')


Insert into ##ProcDef select Pcode from #TmpProcDef order by PSno
insert into ##ProcDef values ('               ')
insert into ##ProcDef values ('GO')
insert into ##ProcDef values ('/*******Proc Ends Here******************/')
insert into ##ProcDef values ('               ')


--- this will help in verifying the Proc

Select @verify_proc1 = 'IF OBJECT_ID(N''' + @pname + ''') IS NOT NULL'
Select @verify_proc2 = @tab + 'PRINT N''<<< CREATED OBJECT '  + @pname +' >>>'''
Select @verify_proc3 = @tab + 'ELSE'
Select @verify_proc4 = @tab + 'PRINT N''<<< **** FAILED CREATING OBJECT '  + @pname + ' >>>'''
Select @verify_proc5 = @Tab + 'GO'
Select @verify_proc6 = @Tab + ' '
--Select @verify_proc7 =
--Select @verify_proc8 =

Insert into ##ProcDef values(@verify_proc1)
Insert into ##ProcDef values(@verify_proc2)
Insert into ##ProcDef values(@verify_proc3)
Insert into ##ProcDef values(@verify_proc4)
Insert into ##ProcDef values(@verify_proc5)
Insert into ##ProcDef values(@verify_proc6)

--Select * from ##ProcDef
--scripting proc into text file
Declare @set_ProcDeftxt varchar(1000)
--SET @set_procdeftxt = ('sqlcmd -S '+@@servername+' -E -q" SET NOCOUNT ON SELECT ltrim(rtrim(Pcode)) FROM rpscdr1_mo.dbo.##ProcDef ORDER BY PSno " -W -h -1 -o "e:\temp\ProcMove\Original\'+ @Pname +'.sql"')
SET @set_procdeftxt = ('sqlcmd -S '+@@servername+' -E -q" SET NOCOUNT ON SELECT ltrim(rtrim(Pcode)) FROM ##ProcDef ORDER BY PSno " -W -h -1 -o "'+ @dirPath +'\'+ @Pname +'.sql"')
--Print @set_procdeftxt


declare @result bit
EXEC @result = master.dbo.xp_cmdShell @set_procdeftxt
IF @result = 0
Print 'All set'
Else
Print 'Error Encountered'

--select len(Pcode) from ##ProcDef
--Select Pcode from ##ProcDef


-- This should be the last line
Select @i = @i + 1
End

cleanup:
Print 'Global ##ProcDef Table already Exist Possibly someone is using it or someone has kept his session opened'
Print 'Exiting the code'

--SELECT ltrim(rtrim(Pcode)) FROM ##ProcDef order by PSno
End

MS Excel : Add quotes to Your Cells in Excel Automatically

How to Add Quotes to Your Cells in Excel Automatically

This tip is going to be mostly helpful to people who working with databases. But, if you work with Excel and find yourself needing to deal with repetitive formatting, you might also appreciate the technique.

When working with CSV files you may need to add quotes to either side of a cell’s contents in order to upload them properly to your database. You can easily find yourself working with an Excel file from a workmate that needs to be formatted.

You got this:

But really need this:

This can be a big damn deal if you have hundreds of fields. No one wants to add this by hand. Fortunately, the solution is a snap! It all has to do with applying specific formatting to your cells.

• Highlight the cells you want to add the quotes.
• Go to Format –> Cells –> Custom
• Copy/Paste the following into the Type field: \”@\”
• Click “okay”
• Be happy you didn’t do it all by hand.

NOTE: It’s always a good idea to open your file in a text editor to double-check your CSV export. Sometimes you need to do a little find/replace action to get your file picture perfect.

========== Second =======

I have found that the concatenate function I have used to add single quotes and a comma to a column of text is no longer working. I need this to bring a series of IDs into a SQL query.

By using following, i was able to achieve desired output.

Basically, it is an alternative to =concatenate():

="'"&A1&"',"

SQL Server : Check Linked servers pointing to Primary or Secondary

-- Script to check Linked servers pointing to Primary or Secondary

Declare @lsrv_name sysname

declare @exctbl varchar(500);

Select @lsrv_name = 'AGN6_SQ1_PL_Test_RO'  -- change linked server name here

If exists (Select 1 from tempdb..sysobjects where xtype = 'u' and name like '#data%')

Begin

Drop table #data 

End 

Create table #data(Replica_type varchar(100), Linked_Srvr Sysname)

If exists (select * from sys.sysservers where srvname like @lsrv_name)

Begin

set @exctbl = 'SELECT ars.role_desc, '''+ @lsrv_name +''' as Linked_srv_name

FROM ' + @lsrv_name +'.master.sys.dm_hadr_availability_replica_states ars 

INNER JOIN ' + @lsrv_name +'.master.sys.availability_groups ag 

ON ars.group_id = ag.group_id

AND ars.is_local = 1'

print (@exctbl);

insert into  #data  exec(@exctbl)

END

select * from #data

Drop table #data

SQL Server : Guest user status on all databases

Below script will only list the DB's where guest user is enabled.

create table #guest_users

(

ServerName nvarchar(50),

DBName nvarchar(200),

name sysname,

Status nvarchar(50)

)

EXEC sp_MSforeachdb

'USE [?];

Insert into #guest_users

SELECT    @@Servername AS ServerName,

DB_NAME() AS DBName,

name,

case hasdbaccess when 1 then ''Enabled'' else ''Disabled'' end AS Status

FROM sysusers where name like ''guest'' AND hasdbaccess = 1;'

Select * from #guest_users

drop table #guest_users

--- Below script will list the DB's where guest user is enabled/disabled with value 

CREATE TABLE #guest_users

(

       ServerName nvarchar(50),

       DBName nvarchar(200),

       name sysname,

       Status nvarchar(50)

)

EXEC sp_MSforeachdb

'USE [?];

Insert into #guest_users

SELECT @@Servername AS ServerName,

             DB_NAME() AS DBName,

             name,

             case hasdbaccess when 1 then ''Enabled'' else ''Disabled'' end AS Status

       FROM sysusers where name like ''guest'';'

SELECT * FROM #guest_users

DROP TABLE #guest_users

SQL Server : Truncate all Tables of a Database

A script to truncate all tables of a Database in Sql Server.

One of major the problem with table truncation is, we need to remove if there are any foreign key’s defined in other tables which references the columns in the table to be truncated. So to truncate a table we need to first remove all the foreign key references then truncate the table and finally add back the removed foreign key constraints.

SET NOCOUNT ON
GO
--Get the list of all the tables to be truncated
 DECLARE @TablesToBeTruncated AS TABLE
 (Id INT IDENTITY(1,1),TableObjectId INT, TableName SYSNAME,
    SchemaId INT)
INSERT INTO @TablesToBeTruncated
 SELECT ST.object_id,ST.name,ST.schema_id
 FROM sys.Tables ST
 WHERE ST.type = 'U' AND ST.NAME NOT LIKE '#%'
 AND ST.name <> 'sysdiagrams'
 --AND ST.NAME NOT IN ('') -- Specify here the comma separated table names for which truncation is not required
 --AND ST.NAME IN ('') -- Specify here the comma separated table names which needs to be truncated

 --Generate the foreignkeys drop and create back script
DECLARE @CreateScript AS NVARCHAR(MAX), @DropScript AS NVARCHAR(MAX)
SELECT
    ------------DROP SCRIPT--------------------
    @DropScript = ISNULL(@DropScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
     + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(FKey.name)
     + CHAR(10),
     -----------CREATE BACK SCRIPT-------------
    @CreateScript = ISNULL(@CreateScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
     + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' ADD CONSTRAINT ' + QUOTENAME(FKey.name)
     + ' FOREIGN KEY ' + '(' + STUFF(( -- Get the list of columns
                 SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.parent_object_id, FKeyCol.parent_column_id))
                 FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
                 WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
                 ORDER BY FKeyCol.constraint_column_id
                 FOR XML PATH('')),1,1,'') + ')'
     + ' REFERENCES ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
                + QUOTENAME(OBJECT_NAME(FKey.referenced_object_id)) + ' (' + STUFF(( -- Get the list of columns
                SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.referenced_object_id, FKeyCol.referenced_column_id))
                FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
                WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
                ORDER BY FKeyCol.constraint_column_id
                FOR XML PATH('')),1,1,'') + ') '
     + CASE WHEN update_referential_action_desc = 'CASCADE' THEN ' ON UPDATE CASCADE'
            WHEN update_referential_action_desc = 'SET_DEFAULT' THEN ' ON UPDATE SET DEFAULT'
            WHEN update_referential_action_desc = 'SET_NULL' THEN ' ON UPDATE SET NULL'
            ELSE ''
       END
     + CASE WHEN delete_referential_action_desc = 'CASCADE' THEN ' ON DELETE CASCADE'
            WHEN delete_referential_action_desc = 'SET_DEFAULT' THEN ' ON DELETE SET DEFAULT'
            WHEN delete_referential_action_desc = 'SET_NULL' THEN ' ON DELETE SET NULL'
            ELSE ''
       END  + CHAR(10)
 FROM @TablesToBeTruncated Tlist
            INNER JOIN SYS.FOREIGN_KEYS FKey
                ON Tlist.TableObjectId = FKey.referenced_object_id

--PRINT THE TRUNCATION SCRIPT
IF LEN(ISNULL(@DropScript,'')) > 0
 BEGIN
     PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------DROP FOREIGN KEY CONSTRAINTS SCRIPT--------'
     PRINT @DropScript + CHAR(10) + ' GO ' + CHAR(10)
 END

PRINT '--------TRUNCATE TABLES SCRIPT--------'
--TRUNCATE TABLES
DECLARE @id INT,@truncatescript NVARCHAR(MAX)
SELECT @id = MIN(Id)FROM @TablesToBeTruncated
WHILE @id is not null
 BEGIN
     SELECT @truncatescript = 'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(SchemaId)) + '.' + QUOTENAME(TableName)
     FROM @TablesToBeTruncated WHERE Id = @id
     PRINT @truncatescript
     SELECT @id = MIN(Id)FROM @TablesToBeTruncated WHERE Id > @id
 END

IF LEN(ISNULL(@CreateScript,'')) > 0
 BEGIN
     PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------CREATE BACK THE FOREIGN KEY CONSTRAINTS SCRIPT--------'
     PRINT CAST((@CreateScript + CHAR(10) + ' GO ' + CHAR(10)) AS NTEXT)
 END
 GO

SQL Server : Stored Procedures Ignore User’s Permissions

According to this MSDN documentation :

Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller’s permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.

I've came across a situation where proc was having insert statement and user had just read access however user was still able to run proc. Obviously, it has execute on proc but no insert on underlying table. 

The Bottom Line!

If you are working with sensitive data, be aware that your users may still be able to access that data via stored procedures, even if you have explicitly given DENY permission on your sensitive tables.