Posts

compare the table and cdc table columns

select schema_name(schema_id)+'.'+name [Tablename],a.Col  from ( SELECT object_id, STUFF((SELECT  ',' + cc.[name]             FROM sys.columns cc             WHERE  cc.object_id=c.object_id             ORDER BY object_id         FOR XML PATH('')), 1, 1, '') AS Col FROM sys.columns c where c.object_id=1481264532 GROUP BY c.object_id) a join  sys.tables t on a.object_id=t.object_id where t.is_tracked_by_cdc=1 order by name desc select replace(replace(name,'_CT',''),'_','.') as [TableName],a.Col  from ( SELECT object_id, STUFF((SELECT  ',' + cc.[name]             FROM sys.columns cc             WHERE  cc.object_id=c.object_id and cc.name not in ('__$start_lsn', '__$end_lsn', '__$seqval', '__$operation', '__$update_mask','__$command_id')             ORDER BY object_id         FOR XML PATH('')), 1, 1, '') AS Col FROM sys.column
Post a Comment
Read more

When we add a column with default constraint will the Ct version changes or not

use prism /* Creating a table*/ Create table dbo.Prismtest ( Id int primary key identity(1,1), [Name] Varchar(10)) alter table dbo.Prismtest add [Address2] varchar(100) default('abc') not null  select * from dbo.prismtest insert into dbo.Prismtest ([Name]) values ('def' )  select ct.* FROM changetable(changes dbo.Prismtest,1 ) as ct  LEFT JOIN dbo.Prismtest a on a.id=ct.id  -- 54067710    /Checking the data in CT table*/ select change_tracking_Current_version()  -- 54067711  /* Here check the CT version is getting changed or not  -- Important */  Alter table dbo.Prismtest Enable change_tracking WITH (TRACK_COLUMNS_UPDATED = OFF);
Post a Comment
Read more

To Check the permissions of the service accounts

Select P.name As Principal, class_desc As PermissionLevel, permission_name As PermissionGranted, ObjectName = Case class When 0 Then DB_NAME() When 1 Then OBJECT_SCHEMA_NAME(major_id) + N'.' + OBJECT_NAME(major_id) End From sys.database_permissions As DP Inner Join sys.database_principals As P On P.principal_id = DP.grantee_principal_id Where permission_name In ('insert', 'update', 'delete', 'control', 'alter') And state = 'G'; -- Grant
Post a Comment
Read more

Count and Unique identifier value

---------------  Count in the table --------------- select count(1) from table sp_spaceused '[audit].ChangeRequestSecurityVulnerabilityHistory' ----------------- Inserting the data in the column of unique identifier ---------- [Guid] [uniqueidentifier] NOT NULL --- insert the data in Unique identifier NEWID ( )
Post a Comment
Read more

Executing the function with the table type and parameter

use prism DECLARE @Variable AS audit.ChangeRequestActivityQueueType  INSERT INTO @Variable([ChangeRequestId],[Guid],[Timestamp],[ChangeRequestHistoryTransactionId],[CommentHistoryId])  VALUES(12345,NEWID ( ),'2-21-2020',12345,12345)  SELECT * from  [audit].[GetChangeRequestSecurityUpdateActivities](@StudentVariable)  
Post a Comment
Read more

T-SQL Script to find High CPU Queries

SELECT TOP 50 Convert(varchar, qs.creation_time, 109) as Plan_Compiled_On, qs.execution_count as ' Total Executions ', qs.total_worker_time as ' Overall CPU Time Since Compiled ', Convert(Varchar, qs.last_execution_time, 109) as ' Last Execution Date / Time ', cast(qs.last_worker_time as varchar) +'    ( '+ cast(qs.max_worker_time as Varchar)+' Highest ever ) ' as ' CPU Time for Last Execution ( Milliseconds ) ', Convert(varchar,(qs.last_worker_time/(1000))/(60*60)) + ' Hrs ( i . e . ' + convert(varchar,(qs.last_worker_time/(1000))/60) + ' Mins & ' + convert(varchar,(qs.last_worker_time/(1000))%60) + ' Seconds ) ' as ' Last Execution Duration ', qs.last_rows as ' Rows returned ', qs.total_logical_reads/128 as ' Overall Logical Reads ( MB ) ', qs.max_logical_reads/128 ' Highest Logical Reads ( MB ) ', qs.last_logical_reads/128 ' Logi
Post a Comment
Read more

sql-server-full-text-search-contains-function

select top 2 * from dbo.OrbitSearchCacheLongTexts where fieldid=22  SELECT * FROM dbo.OrbitSearchCacheLongTexts WHERE CONTAINS(IndexedWords,'"*12345*"') and fieldid=22 SELECT * FROM dbo.OrbitSearchCacheLongTexts where IndexedWords like '%12345%' and fieldid=22
Post a Comment
Read more