VI Editor Commands

Common Vi / Vim File Savings Related Commands (ex mode)

You need to press [Esc] key followed by the colon (:) before typing the following commands:

Command	Description
q	Quit
q!	Quit without saving changes i.e. discard changes
r fileName	Read data from file called fileName
wq	Write and quit (save and exit)
w fileName	Write to file called fileName (save as)
w! fileName	Overwrite to file called fileName (save as forcefully)

Query To Get Table Size and Rowcount

 SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
    (SUM(a.total_pages) * 8 )/1024 AS TotalSpaceMB ,
    (SUM(a.used_pages) * 8 )/1024 AS UsedSpaceMB,
    ((SUM(a.total_pages) - SUM(a.used_pages)) * 8)/1024 AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, p.Rows
ORDER BY
     TotalSpaceMB desc

Information About SQL Agent - TSQL Jobstep

SELECT *
FROM msdb.dbo.sysjobs
WHERE job_id = CAST(0xBAD836E3D331B44BA4CCAC400D244B17  AS UNIQUEIDENTIFIER)

---0xBAD836E3D331B44BA4CCAC400D244B17  <--will be="" font="" id="" job="">

DBGrowth - Query

-- This will create a table in MSDB DB.
-- WWSQLDBGrowth will be the table name


if (select count(1)from msdb..sysobjects where name = 'WWSQLDBGrowth' and xtype = 'U') = 0
-- not exists ((select * from msdb..sysobjects where id = object_id(N'[dbo].[WWSQLDBGrowth]') and OBJECTPROPERTY(id, N'IsUserTable') = 1))
begin
CREATE TABLE [MSDB]..[WWSQLDBGrowth] (
[DBName] [varchar] (255),
[FileGroup] [varchar] (255),
[FileName] [varchar] (255),
[FilePath] [varchar] (1000),
[SizeInMB] [float],
[UsedInMB] [float] ,
[FreeInMB] [float],
[AuditDate] datetime
) ON [PRIMARY]
end
DECLARE @DBName varchar(255)
DECLARE DBSize_cursor CURSOR FOR
select name from master..sysdatabases (nolock) --where status < 48
OPEN DBSize_cursor
 FETCH NEXT FROM DBSize_cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
 exec ('use ' + @DBName + ' insert into [MSDB]..[WWSQLDBGrowth] select ''' + @DBName + ''' as DBName ,  groupname as FileGroup,  name as FileName, filename as FilePath, (size*8)/1024. as SizeInMB , (fileproperty(name, ''SpaceUsed''  )*8)/1024. as UsedInMB , ((size - fileproperty(name,''SpaceUsed''))*8)/1024. as FreeInMB, Getdate() as AuditDate FROM sysfiles a LEFT JOIN sysfilegroups b ON a.groupid = b.groupid')
   FETCH NEXT FROM DBSize_cursor INTO @DBName
END
CLOSE DBSize_cursor
DEALLOCATE DBSize_cursor
GO

Query to list the computed columns indexes

select distinct object_name(syscolumns.id),
syscolumns.name As CalculatedColumn ,
syscomments.text as TheCalculation,
isnull(object_name(SYSINDEXKEYS.id),'No Index Using This CalculatedColumn') As IndexName
from syscolumns
inner join syscomments on syscolumns.id=syscomments.id
left outer join sysindexes
on syscolumns.id=sysindexes.id
left outer join SYSINDEXKEYS ON sysindexes.ID=SYSINDEXKEYS.ID
AND sysindexes.INDID=SYSINDEXKEYS.INDID
and SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
where iscomputed <> 0

To add article to existing Oracle -SQL Replication

Declare @Table_Owner varchar (4000)
Declare @Table varchar (4000)
Declare @Schema varchar (8000)
Declare @Str_Ins varchar (4000)
Declare @Str_Upd varchar (4000)
Declare @Str_Del varchar (4000)

Set @Table_Owner = N'OWNERNAME'
Set @Table = N'ARTICLENAME'
Set @Schema = @Table_Owner+@Table
Set @Str_Ins = N'CALL sp_MSins_'+@Schema+''
Set @Str_Upd = N'CALL sp_MSupd_'+@Schema+''
Set @Str_Del = N'CALL sp_MSdel_'+@Schema+''



-- Adding the transactional articles



use [distribution]

exec sp_addarticle @publication= N'Pegasus_OPS', @article= N' ARTICLENAME',
@publisher= N'PEGPDB', @source_owner= N'DOOR_PRD', @source_object= N'ARTICLENAME',
@type= N'logbased', @description= N'', @creation_script= N'', @pre_creation_cmd= N'drop',
@schema_option= 0x0000000000000083, @use_default_datatypes= 1, @destination_table= N'ARTICLENAME',
@destination_owner= N'dbo', @status= 16, @vertical_partition= N'false',@force_invalidate_snapshot=1,
@ins_cmd= @Str_Ins, @del_cmd= @Str_Del, @upd_cmd= @Str_Upd

Go

To change the Default Backup Path

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N    'D:\VALID PATH'
GO