SqlWritesql PowershellWriteSql

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

2015-08-13T09:13:00.000-04:00

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.

2014-08-01T11:51:00.002-04:00

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

2012-09-06T12:44:00.002-04:00

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

2012-07-11T12:35:00.003-04:00


-- 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

2011-08-29T18:16:00.003-04:00

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

2011-08-17T12:51:00.003-04:00

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.

2010-08-02T19:52:00.001-04:00

--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

2010-07-07T10:47:00.000-04:00

-- 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

2010-06-30T18:41:00.000-04:00

-- 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