Open-SSMS function. Open SSMS using powershell command prompt.

Open SQL Server Management Server using powershell command prompt.


Since working with powershell while now. I got use to open all the tools using powershell command line. I have below list function.

	Open-SSMS {
	[CmdletBinding( DefaultParameterSetName='Ins-Que' )]
	param(
	[Parameter( Position=1,
	Mandatory=$false,
	ValueFromPipelineByPropertyName=$true,
	ValueFromRemainingArguments=$false )]
	[ValidateSet("2012", "2014")]
	[string]
	$version="2014"
	)
	if ($version -eq "2014")
	{
	Invoke-Item "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"
	}
	if ($version -eq "2012")
	{
	Invoke-Item "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
	}
	} #Open-SSMS

view raw Open-SSMS.ps1 hosted with ❤ by GitHub

SQL Login is locked? instead of looking at one at time on SSMS. Here is the script that will check all SQL Sever login at same time .

	select name,
	'Status' = CASE (select LOGINPROPERTY(name,'islocked'))
	WHEN 0 THEN 'Unlocked'
	WHEN 1 THEN 'Locked'
	WHEN NULL then ' '
	END
	from master..syslogins
	where isntname = 0

view raw SQL Login is locked.sql hosted with ❤ by GitHub

Thanks

Genarate DBCC SHRINKFILE command script using Powershell

Generate DBCC SHRINKFILE shrink file command in small size as time

	#Generate shrink script using Powershell
	clear
	$strDB = 'MyDB'
	$strFile = 'MyDBdata'
	$iIntveral = 1000
	$iTotal = 10000
	$iMinMB = 1001
	for ($iTotal ; $iTotal -gt $iMinMB; $iTotal=$iTotal-$iIntveral)
	{
	Write-Host ' USE ['$strDB']' `n `
	'GO' `n `
	'DBCC SHRINKFILE (N'''$strFile''' ,'$iTotal')' `n `
	'GO'
	}
	# Done

view raw Generate DBCC SHRINKFILE.ps1 hosted with ❤ by GitHub

# OUTPUT USE [ MyDB] GO DBCC SHRINKFILE (N' MyDBdata' ,10000) GO USE [ MyDB] GO DBCC SHRINKFILE (N' MyDBdata' ,9000) GO USE [ MyDB] GO DBCC SHRINKFILE (N' MyDBdata' ,8000) GO USE [ MyDB] GO DBCC SHRINKFILE (N' MyDBdata' ,7000) GO USE [ MyDB] GO DBCC SHRINKFILE (N' MyDBdata' ,6000) GO USE [ MyDB] GO DBCC SHRINKFILE (N' MyDBdata' ,5000) GO USE [ MyDB] GO DBCC SHRINKFILE (N' MyDBdata' ,4000) GO USE [ MyDB] GO DBCC SHRINKFILE (N' MyDBdata' ,3000) GO USE [ MyDB] GO DBCC SHRINKFILE (N' MyDBdata' ,2000) GO #>

Backup and Restore Database status indicator

-- Backup and Restore Database status indicator
 
SELECT [spid] = r.session_id,
    substring(t.[text],charindex('[',t.[text],1)+1,(charindex(']',t.[text],1)-charindex('[',t.[text],1))-1) restore_db, r.start_time,
 percent_complete = round(r.percent_complete,2),
 DATEDIFF(mi,r.start_time,getdate()) 'elapsed_time (min)',
    r.[status],
    r.command,
    t.[text] sql_text,
case r.percent_complete
when 0 THEN 1  
else DATEADD(mi,round(DATEDIFF(mi,r.start_time,getdate())/(r.percent_complete/100),0),r.start_time) 
end as 'estimated_completion'
FROM
    sys.dm_exec_requests AS r
CROSS APPLY
    sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE
    r.session_id <> @@SPID
    AND r.command in ('RESTORE DATABASE','BACKUP DATABASE')

Generate trigger script using sys.all_sql_modules in SQL Server 2008

Generate trigger script using sys.all_sql_modules in SQL Server 2008

-- Generate trigger script using sys.all_sql_modules in SQL Server 2008
Select definition+CHAR(10)+'GO' AS '--definition' from sys.all_sql_modules 
where object_id in 
 (Select object_id  from sys.objects where type = 'TR')

List profile without user in oracle

Folling pl/sql script with give you List profile without user in oracle.

--List profile without user in oracle

Select distinct p.PROFILE 
from dba_profiles p, dba_users u 
where p.PROFILE  = u.PROFILE (+) and  u.USERNAME is null

Create dump device dynamicly on the fly.

--dump device Create dynamicly

-- dumpdevice full diskbakup
Select 'EXEC master.dbo.sp_addumpdevice  @devtype = N''disk'', @logicalname = N''' + name + '_DUMP'', @physicalname = N''E:\MSSQL\BACKUP\' + name + '_DUMP.BAK'''
from sys.sysdatabases

-- dumpdevice Diff diskbackup
Select 'EXEC master.dbo.sp_addumpdevice  @devtype = N''disk'', @logicalname = N''' + name + '_DIFFDUMP'', @physicalname = N''E:\MSSQL\BACKUP\' + name + '_DIFFDUMP.BAK'''
from sys.sysdatabases

DBO OFF Script

-- DBO OFF Script help DBA put all or some database out of DBO mode after maintenance or DBA activities.


-- SQL 2000


-- SQL 2005 /2008

DECLARE @vbcrlf varchar(100)

Select @vbcrlf = char(13) + char(10)
select 'USE master' + @vbcrlf +
'GO' + @vbcrlf +
'ALTER DATABASE ['+ name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' 
+ @vbcrlf + 'GO' + @vbcrlf +
'ALTER DATABASE ['+ name + '] SET SINGLE_USER ' 
+ @vbcrlf + 'GO' + @vbcrlf +
'ALTER DATABASE ['+ name + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE' 
+ @vbcrlf + 'GO' + @vbcrlf +
'ALTER DATABASE ['+ name + '] SET MULTI_USER'
+ @vbcrlf + 'GO' + @vbcrlf
from sys.sysdatabases(nolock) where dbid>4 order by name

Learning SQL

DBO ON Scripts

-- DBO on Script help DBA put all or some database into DBO mode for maintenance or DBA activities.


-- SQL 2000


-- SQL 2005 /2008

DECLARE @vbcrlf varchar(100)
Select @vbcrlf = char(13) + char(10)

select 'USE master' + @vbcrlf +
'GO' + @vbcrlf +
'ALTER DATABASE ['+ name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' 
+ @vbcrlf + 'GO' + @vbcrlf +
'ALTER DATABASE ['+ name + '] SET SINGLE_USER ' 
+ @vbcrlf + 'GO' + @vbcrlf +
'ALTER DATABASE ['+ name + '] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
+ @vbcrlf + 'GO' + @vbcrlf +
'ALTER DATABASE ['+ name + '] SET RESTRICTED_USER'
+ @vbcrlf + 'GO' + @vbcrlf
from sys.sysdatabases(nolock) where dbid>4 order by name

Learning SQL