Fuzzy Lookup Transformation: SQL Server DBA general queries

TOPIC: SQL Server - Move file from one location to different location

-- Detail : Move file to different drive
[FileMovement]
(
@DirPath Varchar(1000),
@Dest_Path Varchar(1000)
)
As
Begin
Declare @Qry Varchar(1000)
Set @Qry = 'Move '+@DirPath+' '+@Dest_Path
--Print @Qry
Exec Xp_cmdshell @Qry, no_output
End
GO

TOPIC: SQL Server - Folder and File details

-- Detail : List of files with directory name and creation date

[FileList_SP_Size]

(

@DirPath NVarchar(255)

)

Declare @SQL Nvarchar(255)

Create table #tmp

(

Txt nvarchar(500),

Dir nvarchar(500),

[FileSize] nvarchar(100)

)

Set @SQL = 'Dir/aa/s/p '+CONVERT(NVarchar(255),@DirPath)+'*.*'

Insert Into #tmp(Txt)

Exec Xp_cmdshell @SQL --'Dir/aa/s/p '+CONVERT(NVarchar(255),@DirPath)+'*.*'

Print(@SQL)

Alter Table #tmp Add [Date1] nvarchar(50)

Delete from #tmp

Where txt is null

--OR txt Like ' Directory%'

OR txt Like '%file%bytes%'

OR txt Like '%Volume%ser%'

OR txt Like 'Volume%in%'

OR txt = '%bytes%free%'

OR Ltrim(txt) = ''

Update #tmp

Set txt = Ltrim(Rtrim(SUBSTRING(txt,40,200))),

[FileSize]= Replace(Ltrim(Rtrim(SUBSTRING(txt,25,15))),',',''),

[Date1] = Left(txt,20)

Where txt not Like ' Directory%'

Delete from #tmp

Where txt is null

OR txt = '%bytes%free%'

OR Ltrim(txt) = ''

Alter Table #tmp Add Rowid int IDENTITY(1,1)

Update #Tmp Set Dir = Txt

Where txt Like ' Directory%'

Declare @Rowid int ,@Txt Nvarchar(255),@Dir Nvarchar(255)

Declare File_CURSOR Cursor for

Select Rowid,Txt,Dir from #tmp Where txt Like ' Directory%' Order By Rowid Desc

open File_CURSOR

Fetch next from File_CURSOR INTO @Rowid ,@Txt ,@Dir

while @@Fetch_Status=0

BEGIN

Update #Tmp Set Dir = @Dir

Where Rowid >= @Rowid

And Dir is null

Fetch next from File_CURSOR INTO @Rowid ,@Txt ,@Dir

END

CLOSE File_CURSOR

DEALLOCATE File_CURSOR

Update #Tmp Set Dir = Replace(Dir,' Directory of','')

Select Rowid, Txt As [File name],Dir As [Directory Name] , [FileSize],[Date1] from #Tmp

Where Txt Not Like 'bytes%free%'

And Date1 is not null

TOPIC: SQL Server - Backup Latest Backup Details

-- Detail : SP to find the latest backup

/* Parameter @Avoid_CopyOnly

-- 1 == Will not display copy only backups

-- 0 == Will display all type of backups */

[Find_Latest_Backup]

(

@DB_Name Varchar(100),

@Avoid_CopyOnly Bit = 1

)

BEGIN

SET NOCOUNT ON

Select Top 100

backupset.database_name ,

backupmediafamily.physical_device_name ,

Case When [type] = 'D' AND is_copy_only = 1 Then 'FULL (COPY ONLY)'

When [type] = 'D' AND is_copy_only = 0 Then 'FULL'

When [type] = 'I' AND is_copy_only = 1 Then 'Differential (COPY ONLY)'

When [type] = 'I' AND is_copy_only = 0 Then 'Differential'

When [type] = 'L' Then 'Log'

When [type] = 'F' Then 'File or filegroup'

When [type] = 'G' Then 'Differential file'

When [type] = 'P' Then 'Partial'

When [type] = 'Q' Then 'Differential partial'

Else '' End as Backup_Type

,backupset.name As Backup_Name,backupset.user_name

,CONVERT(date, msdb.dbo.backupset.backup_finish_date, 101) as finish_date

, Convert(Decimal(10,2),msdb.dbo.backupset.backup_size/1000/1000) as backupsizeMB

,Convert(Decimal(10,2),backupset.compressed_backup_size /1000/1000) as compressed_backup_size_MB

Into #Bkp1

FROM

msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset

ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

Where backupset.database_name = @DB_Name

And is_copy_only = 0

Order By backupset.backup_finish_date Desc

Select Top 100

backupset.database_name ,

backupmediafamily.physical_device_name ,

Case When [type] = 'D' AND is_copy_only = 1 Then 'FULL (COPY ONLY)'

When [type] = 'D' AND is_copy_only = 0 Then 'FULL'

When [type] = 'I' AND is_copy_only = 1 Then 'Differential (COPY ONLY)'

When [type] = 'I' AND is_copy_only = 0 Then 'Differential'

When [type] = 'L' Then 'Log'

When [type] = 'F' Then 'File or filegroup'

When [type] = 'G' Then 'Differential file'

When [type] = 'P' Then 'Partial'

When [type] = 'Q' Then 'Differential partial'

Else '' End as Backup_Type

,backupset.name As Backup_Name,backupset.user_name

,CONVERT(date, msdb.dbo.backupset.backup_finish_date, 101) as finish_date

, Convert(Decimal(10,2),msdb.dbo.backupset.backup_size/1000/1000) as backupsizeMB

,Convert(Decimal(10,2),backupset.compressed_backup_size /1000/1000) as compressed_backup_size_MB

Into #Bkp2

FROM

msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset

ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

Where backupset.database_name = @DB_Name

And is_copy_only = 1

Order By backupset.backup_finish_date Desc

If(@Avoid_CopyOnly =0)

Begin

Select * from #Bkp1

Union All

Select * from #Bkp2

Order By finish_date desc

End

If(@Avoid_CopyOnly =1)

Begin

Select * from #Bkp1 Order By finish_date desc

End

END

TOPIC: SQL Server - Generic Email Notification

-- Detail : Generic email

--[Generic_Notification_Mail] @profile_name_P = 'SQL_Mail_Alert', @Subj = 'Test generic email',@Body1 = 'This is a test email. Please ignore.',@Email = 'abc@xyz.com',@CC=''

[Generic_Notification_Mail]

(

@profile_name_P Varchar(255),

@Subj Varchar(500),

@Body1 Varchar(4000),

@Email Varchar(255),

@CC Varchar(255)

)

BEGIN

Declare @Today Date

Select @Today = CONVERT(Date,Getdate())

Declare @Body_Main Varchar(8000)

Select @Body_Main = ''

Select @Body_Main = @Body_Main + '<td>Hi All,</td> </tr>'

Select @Body_Main = @Body_Main + '<td>'+@Body1+' </td> '

Select @Body_Main = @Body_Main+' <td>Thanks & Regards,</td> </tr>'

Select @Body_Main = @Body_Main+'<td>Team Name</td> </tr>'

Select @Body_Main = @Body_Main+' '

Select @Body_Main = @Body_Main+'<td>Note : System generated mail, For any concern revert back to XXX team (abc@xyz.COM).</td></tr>'

Print 'Notification code'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @profile_name_P, --'SQL_Mail_Alert',

--@recipients= @Recipient_List,

@recipients= @Email,

@copy_recipients =@CC,

@subject = @Subj,

@body = @Body_Main,

@body_format = 'HTML'

End

TOPIC: SQL Server - Index Rebuild / Re-Organize for all user DBs

-- Index rebuild for all user db

[IndexRebuild_All_DB]

SET NOCOUNT ON

DECLARE @Database VARCHAR(255)

DECLARE @Table VARCHAR(255),@Table_Short VARCHAR(255)

DECLARE @cmd NVARCHAR(4000)

DECLARE @fillfactor INT ,@bit Bit

--DECLARE @Avg_Frag Float

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR

SELECT [name] FROM sys.databases

Where user_access = 0 And [state] = 0

And database_id >= 5

And [Name] Not In('ReportServer$SQL','ReportServer$SQLTempDB','distribution')

--And [Name] in('SONORA','CNA')

ORDER BY 1

--=============================

-- Temp table for logging

--=============================

IF OBJECT_ID('tempdb.dbo.#ObjDetails') IS NOT NULL

DROP TABLE #ObjDetails

CREATE TABLE #ObjDetails (

Rowid Int Identity(1,1),

DBName Varchar(200),

Table_Name Varchar(200),

Avg_Frag Float,

[Action] Varchar(30),

Dt_Stamp Datetime Default(Getdate())

)

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN

Print 'DB Name ==>'+DB_Name(DB_ID(@Database))

SET @cmd = 'Insert Into DBA_Admin..Table_Fragment_Before(database_id,object_id,avg_fragmentation_in_percent) '+CHAR(10)

SET @cmd = @cmd + ' Select database_id,NULL,NULL FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(' +Quotename(@Database,'''') + ' ),NULL,NULL,NULL,NULL) F Where Object_Name(object_id)= ' +Quotename(@Table_Short,'''')+CHAR(10)

--Print(@cmd)

Exec (@cmd)

SET @cmd = ' Insert Into #ObjDetails(DBName,Table_Name,Avg_Frag,[Action])' +CHAR(10)

SET @cmd = @cmd + ' Values( ''' +@Database + ''', '+Quotename('Start Processing','''')+',0,'''+'DB'+''')'+Char(10)

--Print(@cmd)

Exec (@cmd)

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +

table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES

WHERE table_type = ''BASE TABLE'' '

-- And table_name = ''SONORAREPOSITORY'' '

-- create table cursor

EXEC (@cmd)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table

WHILE @@FETCH_STATUS = 0

BEGIN

Select @Table_Short = Reverse(Substring(Reverse(@Table),2,CHARINDEX('[',Reverse(@Table),0 )-2))

--Print ('Table Name ==> '+@Table_Short)

SET @cmd = 'Insert Into DBA_Admin..Table_Fragment_Before(database_id,object_id,avg_fragmentation_in_percent) '+CHAR(10)

SET @cmd = @cmd + ' Select database_id,object_id,avg_fragmentation_in_percent FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(' +Quotename(@Database,'''') + ' ),NULL,NULL,NULL,NULL) F Where Object_Name(object_id)= ' +Quotename(@Table_Short,'''')+CHAR(10)

--Print(@cmd)

Exec (@cmd)

-- SQL 2005 or higher command

SET @cmd = 'Declare @a bit,@Avg_Frag Float = 0'+CHAR(10)

SET @cmd = @cmd + 'Select @Avg_Frag = ISNULL(avg_fragmentation_in_percent,0) FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(' +Quotename(@Database,'''') + ' ),NULL,NULL,NULL,NULL) F Where Object_Name(object_id)= ' +Quotename(@Table_Short,'''')+' And avg_fragmentation_in_percent > 0'+CHAR(10)

SET @cmd = @cmd + ' If (@Avg_Frag > 30)'+CHAR(10)

SET @cmd = @cmd + ' Begin '+CHAR(10)

SET @cmd = @cmd + ' Print '+'''RB'''+CHAR(10)

SET @cmd = @cmd +' ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' +CHAR(10)

SET @cmd = @cmd + ' Insert Into #ObjDetails(DBName,Table_Name,Avg_Frag,[Action])' +CHAR(10)

SET @cmd = @cmd + ' SELECT ''' +@Database + ''', Table_Name,@Avg_Frag,'''+'RB'+''' From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = ' +Quotename(@Table_Short,'''')+CHAR(10)

SET @cmd = @cmd + ' End '+CHAR(10)

SET @cmd = @cmd + ' Else If (@Avg_Frag Between 1 And 30)'+CHAR(10)

SET @cmd = @cmd + ' Begin '+CHAR(10)

SET @cmd = @cmd + ' Print '+'''RO'''+CHAR(10)

SET @cmd = @cmd +' ALTER INDEX ALL ON ' + @Table + ' REORGANIZE ' +CHAR(10)

SET @cmd = @cmd + ' Insert Into #ObjDetails(DBName,Table_Name,Avg_Frag,[Action])' +CHAR(10)

SET @cmd = @cmd + ' SELECT ''' +@Database + ''', Table_Name,@Avg_Frag,'''+'RO'+''' From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = ' +Quotename(@Table_Short,'''')+Char(10)

SET @cmd = @cmd + ' End '+CHAR(10)

SET @cmd = @cmd + ' Else '+CHAR(10)

SET @cmd = @cmd + ' Begin '+CHAR(10)

SET @cmd = @cmd + ' Set @a = 100'+CHAR(10)

-- SET @cmd = @cmd + ' Print '+'''N/A'''+CHAR(10)

SET @cmd = @cmd + ' End '+CHAR(10)

-- Print(@cmd)

Exec (@cmd)

FETCH NEXT FROM TableCursor INTO @Table

END

CLOSE TableCursor

DEALLOCATE TableCursor

Exec sp_MSForEachtable 'update statistics ? with fullscan'

FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

Select * from #ObjDetails

Drop table #ObjDetails

Create Table Table_Fragment_Before

(

database_id Int,

object_id Bigint,

avg_fragmentation_in_percent Float,

DateTimeStamp Datetime Default(Getdate())

)

Create Table Table_Fragment_After

(

database_id Int,

object_id Bigint,

avg_fragmentation_in_percent Float,

DateTimeStamp Datetime Default(Getdate())

)

TOPIC: SQL Server - Empty Folder containts

[Empty_Folder]

( @DirPath Varchar(1000)

)As

BEGIN

DECLARE @CMD Varchar(1000)

select @CMD = 'CD ' + @DirPath

create table #output (output varchar(255) null)

insert #output exec master..xp_cmdshell @CMD

--select [output] As OtptTest from #output --where output is not null

IF NOT EXISTS(select * from #output where output is not null)

BEGIN

Print 'Validation pass - Folder exists'

--Correct the directory path

IF RIGHT(@DirPath,1)= '\'

Select @DirPath = SUBSTRING(@DirPath,1,LEN(@DirPath)-1)

Set @CMD = 'DEL /F /Q /S '+@DirPath+'\*.*'

Print 'Cleanup folder - ' +@DirPath

Exec XP_CmdShell @CMD

END

DROP TABLE #output

END

TOPIC: SQL Server - Check Folder Existence

DECLARE @Status_Flag bit

EXEC [dbo].[CheckFolderExistance]

@Folder_Path = N'D:\DB_Backup',

@Status_Flag = @Status_Flag OUTPUT

[CheckFolderExistance]

( @Folder_Path Varchar(255),

@Status_Flag Bit Output

)As

Begin

Declare @CMD Varchar(1000)

Set @Status_Flag = 0

Select @CMD = 'CD ' + @Folder_Path

create table #output_15 (output varchar(255) null)

insert #output_15 exec master..xp_cmdshell @CMD

-- Check the folder exists (If no value then folder exists; if error value then folder does't exists)

IF NOT EXISTS(select * from #output_15 where output is not null)

Set @Status_Flag = 1

End

TOPIC: SQL Server - Database Growth

[DatabaseGrowth] @pDBName sysname = NULL

BEGIN

SET NOCOUNT ON;

DECLARE @DatabaseName SYSNAME

-- Use current database, if a database name is not specified in input parameter

SET @DatabaseName = ISNULL(@pDBName, DB_NAME())

SELECT backup_start_date AS StartTime

,@DatabaseName AS DatabaseName

,filegroup_name AS FilegroupName

,logical_name AS LogicalFilename

,physical_name AS PhysicalFilename

,CONVERT(NUMERIC(9,2), file_size/1048576) AS FileSizeInMB

,Growth AS PercentageGrowth

FROM (

SELECT b.backup_start_date

,a.backup_set_id

,a.file_size

,a.logical_name

,a.[filegroup_name]

,a.physical_name

,(SELECT CONVERT(numeric(5,2),((a.file_size * 100.00)/i1.file_size)-100)

FROM msdb.dbo.backupfile i1

WHERE i1.backup_set_id =

(

SELECT MAX(i2.backup_set_id)

FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3

ON i2.backup_set_id = i3.backup_set_id

WHERE i2.backup_set_id < a.backup_set_id

AND i2.file_type='D'

AND i3.database_name = @DatabaseName

AND i2.logical_name = a.logical_name

AND i2.logical_name = i1.logical_name

AND i3.type = 'D'

)

AND i1.file_type = 'D'

) AS Growth

FROM msdb.dbo.backupfile a

JOIN msdb.dbo.backupset b

ON a.backup_set_id = b.backup_set_id

WHERE b.database_name = @DatabaseName

AND a.file_type = 'D'

AND b.type = 'D'

) AS Derived

WHERE ISNULL(Growth, 0.0) <> 0.0

ORDER BY logical_name, StartTime

END

TOPIC: SQL Server -All User List

-- Detail : List of users from all databases / specific user

-- Exec USP_UserList_All_DB 'ALL'

-- Exec USP_UserList_All_DB 'xyz'

[USP_UserList_All_DB]

( @UserName Varchar(100)

) As

BEGIN

If not exists(Select 1 from sysobjects where Xtype = 'U' and Name = 'User_Details')

Begin

CREATE TABLE [dbo].[User_Details](

[DBName] [nvarchar](128) NULL,

[DATABASE_ROLE] [sysname] NOT NULL,

[DATABASE_USER] [sysname] NOT NULL,

[TYPE_DESC] [nvarchar](60) NULL,

[CREATE_DATE] [datetime] NOT NULL,

[Timestamp] datetime Default(Getdate())

) ON [PRIMARY]

End

Truncate table User_Details

DECLARE @command varchar(8000)

SELECT @command = 'USE [?] INSERT INTO [DBA_ADMIN].[dbo].[User_Details]

([DBName]

,[DATABASE_ROLE]

,[DATABASE_USER]

,[TYPE_DESC]

,[CREATE_DATE])

SELECT DB_NAME() As DBName,rp.NAME AS DATABASE_ROLE, mp.NAME AS DATABASE_USER, mp.TYPE_DESC,mp.CREATE_DATE

from sys.database_role_members drm

join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)

join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)

order by rp.name '

EXEC sp_MSforeachdb @command

If (ISNULL(@UserName,'')=''OR @UserName = 'ALL')

Select * from [User_Details] Order By DATABASE_USER,Database_Role

Else

Select * from [User_Details] Where DATABASE_USER = @UserName Order By DBName,DATABASE_USER,Database_Role

END

Print 'Select * from [User_Details] Order By DATABASE_USER,Database_Role '

Fuzzy Lookup Transformation

Monday, February 5, 2018

SQL Server DBA general queries - A

No comments:

Post a Comment

My Blog List