T-SQL SCRIPTS

How to get client IP address from sql server

DECLARE @ipaddress varchar(max)

DECLARE @ipaddressLine varchar(max)

DECLARE @pos int

SET nocount on

SET @ipaddress = NULL

Create table #temp_ip (ipLine varchar(200))

INSERT #temp_ip EXEC master..xp_cmdshell 'ipconfig'

SELECT top 1 @ipaddressLine = ipLine FROM #temp_ip

WHERE upper (ipLine) like '%IPV4 ADDRESS%' or upper (ipLine) like '%IP ADDRESS%'

    IF (isnull (@ipaddressLine,'***') != '***')

      BEGIN

       SET @pos = CharIndex (':',@ipaddressLine,1);

       SET @ipaddress = rtrim(ltrim(substring (@ipaddressLine , @pos + 1 ,len (@ipaddressLine) - @pos)))

      END

PRINT @ipaddress

DROP TABLE #temp_ip

SET nocount off


How to Check Port number of a SQL SERVER 

select local_net_address, local_tcp_port  from sys.dm_exec_connections where session_id = @@spid

Changing the database owner for all databases 

sp_msforeachdb 'USE [?]; EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false' 

• Getting Backup information of a server

SELECT  top 100

            bs.database_name,

            bs.backup_size/(1024*1024*1024) NativeBackupSize,

            -- comment folwoing line for non 2008 servers

          bs.compressed_backup_size/(1024*1024*1024) CompressedBackupSize,

            bs.backup_start_date,

            bs.backup_finish_date,

            bmf.physical_device_name,

            *

FROM  msdb..backupmediafamily AS bmf

JOIN  msdb..backupset   AS bs

      ON    bmf.media_set_id  = bs.media_set_id

      AND bs.type = 'D' --or bs.type= 'L'

and bs.database_name not in('master','model','msdb','tempdb')

and bmf.family_sequence_number =1

--AND bs.database_name = '' -- DBNAME

      ---- to see only last backup for each db

--WHERE     bs.backup_set_id IN

--          (     select  max(backup_Set_id) from msdb..backupset

--                group by database_name)

ORDER BY bs.backup_finish_date desc

-- D  FULL BACKUP

 -- I DIFFERENTIAL

 -- L LOG BACKUP
---

• Finding out backup information in always-on replica servers 

SELECT 'SECONDARYSERVER' as servername, bs.database_name AS 'Database Name'

       ,bs.backup_start_date AS 'Backup Start'

       ,bs.backup_finish_date AS 'Backup Finished'

       --,DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)'

       --,bmf.physical_device_name AS 'Backup File'

       ,CASE

              WHEN bs.[type] = 'D'

                     THEN 'Full Backup'

              WHEN bs.[type] = 'I'

                     THEN 'Differential Database'

              WHEN bs.[type] = 'L'

                     THEN 'Log'

              WHEN bs.[type] = 'F'

                     THEN 'File/Filegroup'

              WHEN bs.[type] = 'G'

                     THEN 'Differential File'

              WHEN bs.[type] = 'P'

                     THEN 'Partial'

              WHEN bs.[type] = 'Q'

                     THEN 'Differential partial'

              END AS 'Backup Type'

       ,ROUND((((bs.backup_size / 1024) / 1024) / 1024), 2) AS 'Backup Size (GB)'

       ,ROUND((((bs.compressed_backup_size / 1024) / 1024) / 1024), 2) AS 'Compressed Backup Size (GB)'

FROM [LINKSRV1].MSDB.dbo.backupmediafamily bmf

INNER JOIN [LINKSRV1].MSDB.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id

WHERE bs.type NOT IN (

              'L'

              ,'I'

              )

       AND bs.database_name NOT IN (

              'msdb'

              ,'model'

              ,'master'

              ,'ReportServer'

              ,'ReportServerTempDB'

              )

--ORDER BY bs.backup_start_date DESC

union 

SELECT 'PRIMARY SERVER' as servername, bs.database_name AS 'Database Name'

       ,bs.backup_start_date AS 'Backup Start'

       ,bs.backup_finish_date AS 'Backup Finished'

       --,DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)'

       --,bmf.physical_device_name AS 'Backup File'

       ,CASE

              WHEN bs.[type] = 'D'

                     THEN 'Full Backup'

              WHEN bs.[type] = 'I'

                     THEN 'Differential Database'

              WHEN bs.[type] = 'L'

                     THEN 'Log'

              WHEN bs.[type] = 'F'

                     THEN 'File/Filegroup'

              WHEN bs.[type] = 'G'

                     THEN 'Differential File'

              WHEN bs.[type] = 'P'

                     THEN 'Partial'

              WHEN bs.[type] = 'Q'

                     THEN 'Differential partial'

              END AS 'Backup Type'

       ,ROUND((((bs.backup_size / 1024) / 1024) / 1024), 2) AS 'Backup Size (GB)'

       ,ROUND((((bs.compressed_backup_size / 1024) / 1024) / 1024), 2) AS 'Compressed Backup Size (GB)'

FROM MSDB.dbo.backupmediafamily bmf

INNER JOIN MSDB.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id

WHERE bs.type NOT IN (

              'L'

              ,'I'

              )

       AND bs.database_name NOT IN (

              'msdb'

              ,'model'

              ,'master'

              ,'ReportServer'

              ,'ReportServerTempDB'

              )

ORDER BY bs.backup_start_date DESC

---- Taking T-LOGBACKUPS dynamically AND shrinking the log files.

DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
declare @sqlcmd varchar(1000)
Declare @filesize int
Declare @maxsize int
SET @path = 'c:\Backups\'

　
SELECT @fileDate = replace(replace -- change the path accordingly here
(replace
(CONVERT(CHAR(19), GETDATE(), 120),':', '_'), '-', '_'),' ','_')

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE recovery_model_desc != 'SIMPLE' and name not in('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @path = 'c:\backups\'+@name +'\'
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP LOG @name TO DISK = @fileName WITH INIT,stats=10



Select @filesize = size from sysaltfiles where dbid = DB_ID(@name) and fileid =2
print @filesize
if @filesize>=200000
begin
declare @file_id int
set @file_id=2
set @sqlcmd = 'use ' + QUOTENAME(@name) + '; dbcc shrinkfile ( ' + cast(@file_id as varchar) + ', 1024 );'
exec ( @sqlcmd )
print @sqlcmd

end
--DBCC Shrinkfile(2,1000)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Changing SERVER Level collation:-

setup.exe /ACTION=RebuildDatabase /INSTANCENAME="MSSQLSERVER" /SAPWD="Password” /SQLCollation=SQL_Latin1_General_CP1_CI_AS /SQLSYSADMINACCOUNTS="ServiceAccountName"

************Changing Schema Owners to DBO
select s.name as schema_name, s.schema_id, u.name as schema_owner from sys.schemas s inner join sys.sysusers u on u.uid = s.principal_id where u.issqluser = 1 and u.name not in ('sys', 'guest', 'INFORMATION_SCHEMA')
************************************