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

Unable to expand catalogs on linked Server

ERROR :-

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

Solution :-
 
USE MASTER

GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [LOGIN];