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.
SqlWritesql PowershellWriteSql
SQL script that writes sql (DLL or DML) script for DBA and Developer for MS SQL Server 2005 or 2008 or R2
Thursday, August 13, 2015
Friday, August 1, 2014
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
Thanks
Thursday, September 6, 2012
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 #>
Wednesday, July 11, 2012
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')
Monday, August 29, 2011
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')
Wednesday, August 17, 2011
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
--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
Monday, August 2, 2010
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
Wednesday, July 7, 2010
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
Learning SQL
-- 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
Wednesday, June 30, 2010
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
Learning SQL
-- 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
Subscribe to:
Posts (Atom)