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.

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 .



Thanks

Genarate DBCC SHRINKFILE command script using Powershell

Generate DBCC SHRINKFILE shrink file command in small size as time # 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