--------------Currently user running Query-------
use master
SELECT 'Currently Running Query'as RunningQuery,DATEDIFF(MINUTE, A.last_batch, GETDATE()) RunningTime,
A.spid, A.blocked, A.waittime, db_name(A.dbid) As dbname,B.text as sqlstatement,
A.hostname, A.loginame,A.program_name,A.last_batch,A.nt_username
FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
WHERE A.dbid > 4
---------------------------------Batch requests/sec
DECLARE @BRPS BIGINT
SELECT @BRPS=cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
WAITFOR DELAY '000:00:01'
SELECT @@SERVERNAME AS ServerName,(cntr_value-@BRPS)/10.0 AS "Batch Requests/sec"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
-------------------------------------find most consumed objects/statements info------------------------------
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
ORDER BY qs.total_worker_time DESC -- CPU time
----------------------------------------------find most consumed stored procedure info
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count
,total_worker_time / execution_count AS AVG_CPU
,total_elapsed_time / execution_count AS AVG_ELAPSED
,total_logical_reads / execution_count AS AVG_LOGICAL_READS
,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
,total_physical_reads / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
where database_id<>32767 and DB_NAME(database_id) not in ('master','msdb')
ORDER BY AVG_LOGICAL_READS DESC
----------------------------------------------------------Table Row count----------
SELECT @@servername as servername,DB_NAME() AS [Database Name],T.name AS [TABLE NAME],
I.row_count AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.dm_db_partition_stats AS I
ON T.object_id = I.object_id
AND I.index_id < 2
ORDER BY I.row_count DESC
-------------------------------------------blocking info with dbcc inputbuffer
select distinct top 1 @id=spid from sys.sysprocesses where blocked<>0
set @x=@id
if @x>0
begin
select @x as blockingspid
dbcc inputbuffer(@x)
end
else
select 'nothing blocked' as blockingstatus
---------------------------------------Page life expectancy
SELECT @@SERVERNAME AS ServerName,[object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.
check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.
--------------------------------------Lazy writer------------
SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Lazy writes/sec'
The lazy writer is a process that periodically checks the available free space in the buffer cache between two checkpoints and ensures that there is always enough free memory.
When the lazy writer determines free pages are needed in the buffer for better performance, it removes the old pages before the regular checkpoint occurs
If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache
The Lazy writes metric is defined as "Number of times per second SQL Server relocates dirty pages from buffer pool (memory) to disk"
The threshold value for Lazy Writes is 20
If the Lazy Writes value is constantly higher than 20, to be sure that the server is under memory pressure, check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.
------------------------------check point--------------------------------------------------------------------------------------------------------------------------------------------------------
A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction
at a check point, which can be automatic (occurs automatically to meet the recovery interval request) , indirect (occurs automatically to meet the database target recovery time),
manual (occurs when the CHECKPOINT command is executed), and internal (occurs along with some server-level operations, such as backup creation)
--------------------------------------------------------Latch--------------------------
a latch is a lightweight synchronization object used by the Storage Engine to protect memory structures used internally by SQL Server.
3 Different types of Latches:
IO Latches
Buffer Latches (BUF)
Non-Buffer Latches (Non-BUF)
Latch Modes
Keep
Shared
Update
Exclusive
Destroy
-------------------------------Free list stalls/sec---------------------------
SELECT 'Free list stalls/sec',
cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%' and counter_name LIKE '%Free list stalls/sec%'
----------------------------------------find number of core is SQL using-----------
select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'
------------------------------------To find out Unused DB info------------
SELECT @@ServerName AS server
,NAME AS dbname
,COUNT(STATUS) AS number_of_connections
,GETDATE() AS queryexecutedtimestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
SELECT TOP 10000 s.[database_id]
,d.name
,d.create_date
,s.[index_id]
,s.[object_id]
,s.[user_seeks]
,s.[user_scans]
,s.[user_lookups]
,s.[user_updates]
,s.[last_user_seek]
,s.[last_user_scan]
,s.[last_user_lookup]
,s.[last_user_update]
FROM [master].[sys].[dm_db_index_usage_stats] as s right outer join sys.databases d on s.database_id = d.database_id
where d.database_id > 4
order by d.name
select d.name, x1 =
(select X1= max(bb.xx)
from (
select xx = max(last_user_seek)
where max(last_user_seek) is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan) is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx = max(last_user_update)
where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join
sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
where d.dbid > 4
group by d.name
-----------------------DMV query that will tell us high level information of disk latency
SELECT
*
,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms',(wait_time_ms/waiting_tasks_count)/1000 as 'AVG Wait in Sec',
(wait_time_ms/waiting_tasks_count)/1000/60 as 'AVG Wait in Mint'
FROM
sys.dm_os_wait_stats
WHERE
waiting_tasks_count > 0
ORDER BY
wait_time_ms DESC
----------------------------------------Number of user connections info ---------------
SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0
THEN 'Unused'
ELSE 'Used'
END
, CASE WHEN des.status = 'Sleeping'
THEN 'sleeping'
ELSE 'Not Sleeping'
END
, ConnectionCount = COUNT(1)
FROM sys.dm_exec_connections dec
INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
GROUP BY CASE WHEN des.status = 'Sleeping'
THEN 'sleeping'
ELSE 'Not Sleeping'
END
, CASE WHEN dec.most_recent_sql_handle = 0x0
THEN 'Unused'
ELSE 'Used'
END;
---------------------------------------------Adding tempdb files based on core/processor-------------------------
Select @@servername as servername,DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= 'rows'
SELECT @@servername as servername,cpu_count AS logicalCPUs FROM sys.dm_os_sys_info
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 5MB) --<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev3.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev4',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev4.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev5',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev5.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev6',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev6.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
---ETC, add files as per processors count
------------------------------------------Enable all Agent Jobs-----------------
SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=1'
FROM msdb..DBA_Agent_jobs_Snapshot
--Run output from above
------------------------------------------Disable all Agent Jobs
Select * into msdb..DBA_Agent_jobs_Snapshot
FROM msdb..sysjobs
WHERE ENABLED = 1
SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=0'
FROM msdb..DBA_Agent_jobs_Snapshot
--Run output from above
-----------------------------------Kill the unused session on db bulk------------------------------------------
ALTER DATABASE teis_wfl SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE teis_wfl SET MULTI_USER
----------------------------------------------------------------DB Mail for Backup status report---------------------------------
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @SER NVARCHAR(20);
DECLARE @StrSubject VARCHAR(100);
SET @SER=@@SERVERNAME
SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'
SET @tableHTML =
N'<H1>Database Backup Status Report</H1>' +
N'<table border="1">' +
N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th></tr>' +
CAST ( ( SELECT td = @SER, '',td = B.name, '',
td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end,d.state_desc
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
GROUP BY B.name,B.status,d.state_desc
ORDER BY B.name
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
PRINT @tableHTML
EXEC msdb.dbo.sp_send_dbmail @profile_name='Database Administrator',
@recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
--@recipients = 'JAR@texmo.net',
@subject = @StrSubject,
@body = @tableHTML,
@body_format = 'HTML' ;
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @SER NVARCHAR(20);
DECLARE @StrSubject VARCHAR(100);
SET @SER=@@SERVERNAME
SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'
SET @tableHTML =
N'<H1>Database Backup Status Report</H1>' +
N'<table border="1">' +
N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th><th>Backup_type</th><th>Backup Size in MB</th><th>Backup Size in GB</th></tr>' +
CAST ( ( SELECT td = @SER, '',td = LTRIM(RTRIM(B.name)), '',
td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end, '',td=d.state_desc, '',
td= CASE MST.type
WHEN 'D' THEN 'Full Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'File Level'
WHEN 'G' THEN 'File Level Differential'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END, '',
td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as money),10))), '',td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as INT),10)))/1024, ''
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
and (CONVERT(datetime, MST.backup_start_date, 102) >= GETDATE() - 1)
GROUP BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
ORDER BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
PRINT @tableHTML
EXEC msdb.dbo.sp_send_dbmail @profile_name='Database Administrator',
--@recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
@recipients = 'JAR@texmo.net',
@subject = @StrSubject,
@body = @tableHTML,
@body_format = 'HTML' ;
----------------------------------------------------Backup status query----------------------------------------------------
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end as BackupStatus
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
where B.name not in ('tempdb')
GROUP BY B.name,B.status
ORDER BY B.name
SELECT @@SERVERNAME AS ServerName,B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end as Backup_Status_Today,d.state_desc,
CASE MST.type
WHEN 'D' THEN 'Full Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'File Level'
WHEN 'G' THEN 'File Level Differential'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS backup_type,
convert(varchar,cast(MST.backup_size/1024/1024 as money),10) as 'Backup Size in MB',convert(varchar,cast(MST.backup_size/1024/1024 as INT),10) /1024 as 'Backup Size in GB'
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
--------------------------------------Databases not backed up within 24 hrs----------
DECLARE
@dblist NVARCHAR(MAX) = N'',
@c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
@s SYSNAME = @@SERVERNAME;
SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
+ COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
+ @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
SELECT
bs.database_name AS db,
MAX(bs.backup_finish_date) AS d,
DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age
-------------^^^^ please don't use lazy shorthand like hh
FROM msdb.dbo.backupset AS bs
WHERE [type] = 'D'
GROUP BY database_name
HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -24, GETDATE()))
UNION ALL -- why UNION? By definition there are no duplicates to filter.
-- in fact you could re-write this without two separate queries at all.
SELECT
name AS db,
NULL AS d,
9999 AS age
FROM
master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
WHERE name <> N'tempdb' AND NOT EXISTS
(
SELECT 1 FROM msdb.dbo.backupset
WHERE database_name = d.name
)
) AS x
ORDER BY db;
IF @dblist > N''
BEGIN
PRINT @dblist;
--EXEC msdb.dbo.sp_send_dbmail
-- @recipients = 'dba@someemailaddress.com',
-- @subject = N'Databases not backed up within 24 hrs',
-- @body = @dblist;
END
----------------------------------------------Missing index Bulk-------------------------
EXEC sp_MSforeachdb 'USE ? SELECT ''?''dbname,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
''CREATE INDEX [IX_'' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ''_''
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN ''_''
ELSE ''''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ '']''
+ '' ON '' + dm_mid.statement
+ '' ('' + ISNULL (dm_mid.equality_columns,'''')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN '','' ELSE
'''' END
+ ISNULL (dm_mid.inequality_columns, '''')
+ '')''
+ ISNULL ('' INCLUDE ('' + dm_mid.included_columns + '')'', '''') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC '
------------------------------------To send DB offline status report via db mail------------------------------------
if(select count(*) from sys.databases where state_desc<>'Online')>0
Begin
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H1>Offline Databases Report</H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>Database Status</th></tr>' +
CAST ( ( Select td=name, '',td=state_desc from sys.databases where state_desc<>'Online'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name='ProfileName', --Change to your Profile Name
@recipients='email@domain.com;email1@domain.com', --Put the email address of those who want to receive the e-mail
@subject = 'Offline Databases Report',
@body = @table,
@body_format = 'HTML' ;
END
Else Print 'All Databases are Online'
-------------------------------------Index fragmentation report-----------------------
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
WHEN indexstats.avg_fragmentation_in_percent < 10
THEN 'NOTHING'
WHEN indexstats.avg_fragmentation_in_percent >= 10
AND indexstats.avg_fragmentation_in_percent < 30
THEN 'REORGANIZE'
WHEN indexstats.avg_fragmentation_in_percent >= 30
THEN 'REBUILD'
END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
WHEN indexstats.avg_fragmentation_in_percent < 10
THEN 'NOTHING'
WHEN indexstats.avg_fragmentation_in_percent >= 10
AND indexstats.avg_fragmentation_in_percent < 30
THEN 'REORGANIZE'
WHEN indexstats.avg_fragmentation_in_percent >= 30
THEN 'REBUILD'
END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (14, NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = 14 and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc
Fragmentation is less than 10% – no de-fragmentation is required. It is generally accepted that in majority of environments index fragmentation less than 10% in negligible and its performance impact on the SQL Server is minimal.
Fragmentation is between 10-30% – it is suggested to perform index reorganization
Fragmentation is higher than 30% – it is suggested to perform index rebuild
-------------------------------------------------To view statistics status info against Database-------------------------
SELECT Distinct @@servername as Servername,db_name(db_id()) as DB_Name, SCHEMA_NAME(schema_id) SchemaName,t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated ,'['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'
SELECT Distinct 'Update Statistics'+ '['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'
-------------------------------------------Recovery pending----------------------------
SELECT name, state_desc from sys.databases
where state_desc='RECOVERY_PENDING'
exec sp_resetstatus dbname
ALTER DATABASE dbname SET ONLINE
DBCC CHECKDB('dbname') WITH NO_INFOMSGS
ALTER DATABASE dbname SET SINGLE_USER
DBCC CHECKDB('dbname',REPAIR_REBUILD)
ALTER DATABASE dbname SET MULTI_USER
https://promx.net/en/2016/11/microsoft-sql-server-database-repair-following-recovery-pending-status/
-----------------------------------------Alter Query for move tempdb secondary files-----------------------
Transact-SQL
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''Z:\MSSQL\DATA\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');
------------------------------------------------to check what job is running at a particular time----------------------
SELECT * FROM
(
SELECT JobName, RunStart, DATEADD(second, RunSeconds, RunStart) RunEnd, RunSeconds,RunSeconds/60 Mints,RunSeconds/60/60 hrs
FROM
(
SELECT j.name AS 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) AS 'RunStart',
((jh.run_duration/1000000)*86400)
+ (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600)
+ (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60)
+ (jh.run_duration-(jh.run_duration/100)*100) RunSeconds
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE jh.step_id=0 --The Summary Step
) AS H
) AS H2
WHERE '2018-10-22 03:30:00' BETWEEN RunStart AND RunEnd
ORDER BY JobName, RunEnd
------------------------------------------------Log reuse monitor---------------------------------------------
dbcc sqlperf(logspace)
Select @@SERVERNAME AS ServerName,name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases
where log_reuse_wait_desc<>'NOTHING'
SELECT instance_name
, [Data File(s) Size (KB)] * 1.0 / 1024 [Data File(s) Size (MB)]
, [Log File(s) Size (KB)] * 1.0 / 1024 [Log File(s) Size (MB)]
, [Log File(s) Used Size (KB)] * 1.0 / 1024 [Log File(s) Used Size (MB)]
, [Percent Log Used]
, log_reuse_wait_desc
FROM ( SELECT os.counter_name, os.instance_name, os.cntr_value
, db.log_reuse_wait_desc
FROM sys.dm_os_performance_counters os
JOIN sys.databases db ON os.instance_name = db.name
WHERE os.counter_name IN
(
'Data File(s) Size (KB)'
, 'Log File(s) Size (KB)'
, 'Log File(s) Used Size (KB)'
, 'Percent Log Used'
)
--AND os.instance_name = 'TLogTruncate'
) as SourceTable
PIVOT (
MAX(cntr_value) FOR counter_name IN
([Data File(s) Size (KB)]
, [Log File(s) Size (KB)]
, [Log File(s) Used Size (KB)]
, [Percent Log Used])) as PivotTable
--------------------------------------------To see DB growth for all DB-----------------------------------------------
SELECT 'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END
)
,'growth' = (
CASE is_percent_growth
WHEN 1
THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END
)
,'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id
SELECT @@SERVERNAME as ServerName,*
FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
convert(varchar(10),[Dateofobservation],121) [Date],
SUM([OccupiedinMB]) [OccupiedinMB]
FROM [db_admin].[dbo].[DBGrowth]
GROUP BY [DBName],YEAR([Dateofobservation]),
convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData
PIVOT( SUM([OccupiedinMB])
FOR [Date] IN ( [2018-11-29],[2018-11-30],[2018-12-01],[2018-12-02],[2018-12-03],[2018-12-04],[2018-12-05], [2018-12-06],[2018-12-07],[2018-12-08],[2018-12-09],[2018-12-10],[2018-12-11]
,[2018-12-12],[2018-12-13],[2018-12-14],[2018-12-15],[2018-12-16],[2018-12-17],[2018-12-18],[2018-12-19],[2018-12-20],[2018-12-21],[2018-12-22],[2018-12-23],[2018-12-24]
,[2018-12-25],[2018-12-26],[2018-12-27],[2018-12-28],[2018-12-29],[2018-12-30],[2018-12-31]
)) AS MNamePivot
Declare @s varchar(max)=''
Declare @i int
Declare @j int
set @i=1
select @j=right(convert(varchar(7),getdate(),121),2)
select @j as j
set @s='SELECT @@SERVERNAME as ServerName,*'
set @s=@s +'FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
convert(varchar(10),[Dateofobservation],121) [Date],
SUM([OccupiedinMB]) [OccupiedinMB]
FROM [db_admin].[dbo].[DBGrowth]
GROUP BY [DBName],YEAR([Dateofobservation]),
convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData'
set @s=@s +' PIVOT( SUM([OccupiedinMB])
FOR [Date] IN ( '
set @s=@s+' [2018-'+cast(@j as varchar)+'-01],[2018-'+cast(@j as varchar)+'-02],[2018-'+cast(@j as varchar)+'-03],[2018-'+cast(@j as varchar)+'-04]
,[2018-'+cast(@j as varchar)+'-05],[2018-'+cast(@j as varchar)+'-06],[2018-'+cast(@j as varchar)+'-07],[2018-'+cast(@j as varchar)+'-08]
,[2018-'+cast(@j as varchar)+'-09],[2018-'+cast(@j as varchar)+'-10],[2018-'+cast(@j as varchar)+'-11],[2018-'+cast(@j as varchar)+'-12]
,[2018-'+cast(@j as varchar)+'-13],[2018-'+cast(@j as varchar)+'-14],[2018-'+cast(@j as varchar)+'-15],[2018-'+cast(@j as varchar)+'-16]
,[2018-'+cast(@j as varchar)+'-17],[2018-'+cast(@j as varchar)+'-18],[2018-'+cast(@j as varchar)+'-19],[2018-'+cast(@j as varchar)+'-20]
,[2018-'+cast(@j as varchar)+'-21],[2018-'+cast(@j as varchar)+'-22],[2018-'+cast(@j as varchar)+'-23],[2018-'+cast(@j as varchar)+'-24]
,[2018-'+cast(@j as varchar)+'-25],[2018-'+cast(@j as varchar)+'-26],[2018-'+cast(@j as varchar)+'-27],[2018-'+cast(@j as varchar)+'-28]
,[2018-'+cast(@j as varchar)+'-29],[2018-'+cast(@j as varchar)+'-30],[2018-'+cast(@j as varchar)+'-31]
) '
set @s =@s+') as MNamePivot'
print @s
Exec(@s)
-----------------------------------------To Create and test linked server connection----------------------------------------------------
EXEC sp_addlinkedserver
@server=N'TPCDB1\TPCSQL', -- Remote Computer Name
@srvproduct=N'', -- Not Needed
@provider=N'SQLNCLI', -- SQL Server Driver
@datasrc=N'TPCDB1\TPCSQL'; -- Server Name And Instance
declare @srvr nvarchar(128), @retval int;
set @srvr = '192.168.231.108';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
EXEC sp_addremotelogin 'ACCOUNTS';
To connect linked sever
We need to check default instance/named instance through SQL configuration manager
1)we need to enable TCP/IP and Named Pipes from SQL configuration manager
2)We need to start SQL Browse service from SQL configuration manager
3)we need to allow 1433 from windows firewall advanced security tab for both Inbound and Outbound rule
3) need to add IPADDRESS With machine name on C/windows32/drivers/etc/hosts file
4)SQL authentication only preferred while create connection of linked server
5)Create and Access SQL login should exists on both source and destination login list
6)Sometimes in SQL2005 Express edition and named instances we need to create [IP\instancename,port] in datasource for linked server like below
EXEC sp_addlinkedserver
@server=N'linkeservername', -- Remote Computer Name
@srvproduct=N'', -- Not Needed
@provider=N'SQLNCLI', -- SQL Server Driver
@datasrc=N'192.168.129.1\SQLEXPRESS,1433'; -- Server Name And Instance
but original datasource is MATRIXSERVER\SQLEXPRESS
7)you need to change linked server properties then security tab then be made using login context as sysadmin login with password the sql job will run sysadmin login otherwise linked server using job will run SQL Agent service
------------------------------------------To see MSDB table size info-----------------------------
USE msdb
GO
SELECT TOP(10)
o.[object_id]
, obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
, o.[type]
, i.total_rows
, i.total_size
FROM sys.objects o
JOIN (
SELECT
i.[object_id]
, total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
, total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
FROM sys.indexes i
JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE i.is_disabled = 0
AND i.is_hypothetical = 0
GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC
------------------------------------------------- Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context';
-- Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC;
-- Permissions
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH (NOLOCK)
INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S', 'U', 'G')
ORDER BY server_principals.name ,
server_permissions.state_desc ,
server_permissions.permission_name;
------------------------------sp_help_revlogin-------
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
--EXEC sp_help_revlogin
-------------------------------------------------------------TSQL query to stop long running SQL Job and start it back---------------------------
--if elapsed time is actually needed, uncomment
DECLARE @elapsed_minutes_max int
SET @elapsed_minutes_max = 240 --4 hours, adjust as needed
IF OBJECT_ID('tempdb.dbo.#jobactivity') IS NOT NULL
DROP TABLE #jobactivity
CREATE TABLE #jobactivity (
session_id int NULL,
job_id uniqueidentifier NULL,
job_name sysname NULL,
run_requested_date datetime NULL,
run_requested_source sysname NULL, --1=via schedule; 2=via alert; 3=via startup; 4=via user; 6=via CPU idle.
queued_date datetime NULL,
start_execution_date datetime NULL,
last_executed_step_id int NULL,
last_executed_step_date datetime NULL,
stop_execution_date datetime NULL,
next_scheduled_run_date datetime NULL,
job_history_id int NULL,
message nvarchar(1024) NULL,
run_status int NULL,
operator_id_emailed int NULL,
operator_id_netsent int NULL,
operator_id_paged int NULL
)
INSERT INTO #jobactivity
EXEC msdb.dbo.sp_help_jobactivity
IF EXISTS(
SELECT *
FROM #jobactivity ja
WHERE
ja.start_execution_date IS NOT NULL AND
ja.stop_execution_date IS NOT NULL AND
DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) >= @elapsed_minutes_max
AND ja.job_name = N'Order Planning2')
BEGIN
EXEC msdb.dbo.sp_stop_job N'Order Planning2'
print 'stop it'
END
ELSE
BEGIN
PRINT 'No JOB FOUND'
END
drop table #jobactivity
---------------------------------------Shrink TEMPDB without restart(last try before restart)--------------------------------------------
CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (TEMPDEV, 1024)
GO
-----------------------------------------TSQL code to find table with backedup with date----------------------
sp_tables '%_[1-30][1-12][1991-2020]%'
--------------------------------------General Guidelines to improve query performance-------------
Table should have primary key
Table should have minimum of one clustered index
Table should have appropriate amount of non-clustered index
Non-clustered index should be created on columns of table based on query which is running
Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
Do not to use Views or replace views with original source table
Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
Remove any adhoc queries and use Stored Procedure instead
Check if there is atleast 30% HHD is empty – it improves the performance a bit
If possible move the logic of UDF to SP as well
Remove * from SELECT and use columns which are only necessary in code
Remove any unnecessary joins from table
If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)
-------------- Disable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
----------------------- Enable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
---------To find column name of collation
Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'col_test
----Change Collation of a SQL Server Table Column---------------------
USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
------------------------------Change collation of database----------------------------
USE master;
GO
ALTER DATABASE dbname
COLLATE French_CI_AS ;
GO
Note;
if you change the database collation you need to check whether column of table collation also changed or not. If not you need to change one by one use below script
USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
before change collation of column you need to verify following
You cannot change the collation of a column that is currently referenced by any one of the following:
A computed column
An index
Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
A CHECK constraint
A FOREIGN KEY constraint
When you work with tempdb, the COLLATE clause includes a database_default option to specify that a column in a temporary table uses the collation default of the current user database for the connection instead of the collation of tempdb.
see for more
https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-2017
---------------------Verify the collation setting for database
SELECT name, collation_name
FROM sys.databases
WHERE name = N'dbname';
------------------------------------------------------------------------------------
ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [msdb] SET DISABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET ENABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET MULTI_USER
EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_start_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
-------------------------table with rowcount and size------------------
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
--WHERE
-- t.is_ms_shipped = 0
-- AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UsedSpaceMB DESC, t.Name
------------------------------How much Memory SQL DB used----------------------------------------------
SELECT
TOP 10 DB_NAME(database_id) Database_Name,
COUNT (*) * 8 / 1024 AS MBUsed,COUNT (*) * 8 / 1024 /1024 GBused
FROM
sys.dm_os_buffer_descriptors
where DB_NAME(database_id) is not null
GROUP BY
database_id
ORDER BY
COUNT (*) * 8 / 1024 DESC
---------------------------------------------index fragment based on conditions-----
Depending on fragmentation index level appropriated action will be taken (no action, rebuild or reorganize)
If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index will be reorganized, finally if index average fragmentation is greater than 30% index will be rebuilt.
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SET @dbid = DB_ID();
SELECT
[object_id] AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
AND index_id > 0 -- Ignore heaps
AND page_count > 25; -- Ignore small tables
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
-----------------------------------------------------------------------------------------------
Phases of recovery(SQL Server)
the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.
Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is built of the uncommitted transactions at the time of the SQL Server stopped also.
Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right back at the oldest open transaction so that the neccessary locks can be aquired.
Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.
Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).
---------------------------------------------------------VLF and log file Info------------------------------
Understanding the VLF(Virtual Log File)
A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file. SQL server uses the transaction log in sequential manner.As the data file divided into pages,log files are divided into virtual log file(VLF).The size of the VLFs in a log file may not be in equal size. SQL server decide the size and number of VLF in a log file based on the size of the log file growth as given below.
Growth upto 64 MB = 4 VLF
From 64 MB to 1 GB = 8 VLF
Larger than 1 GB = 16 VLF
dbcc loginfo--------VLF info
dbcc sqlperf(logspace)-------------
ref:
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/09/30/sql-server-part-1-architecture-of-transaction-log/
--From SQL 2017 To know VLF Count below DMV can be used
SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC
GO
-----------------------------Change collation of SQL Server---------------
you need to login to Domain\administrator account on RDP or machine
To rebuild system databases for an instance of SQL Server:
Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.
From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
Default instance:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P2\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP850_BIN
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P1\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
Named Instance:
some time we will get error The Windows account admin-pc\administrator does not exist and cannot be provisioned as a SQL Server system administrator we need to use "" on account as below
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="admin-pc\administrator" /SAPWD= texmo$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2014
https://www.sanssql.com/2012/10/how-to-change-server-collation.html
https://www.sanssql.com/2013/10/change-sql-server-collation-without.html
Named instance:
https://dba.stackexchange.com/questions/143246/error-when-trying-to-install-sql-server-2012-ent-in-to-a-server-core-2008-vm
Note:
Need to login to server via Domainname\administrator and need to know sa account password
Before do we need take system dbs and users db backup with logins and linked servers
as user dbs will be removed after complete rebuild of systemdbs for server collation changes
-------------------------------------------Permuations and Combinations----------------------------------
--option 1
Create table #pk(col1 varchar(100),col2 varchar(100),primary key(col1,col2))
declare @t1 table (col1 varchar(100))
insert @t1
select 'Data1' UNION
select 'Data2' UNION
select 'Data3' UNION
select 'Data4' UNION
select 'Data5'
Declare @t2 table (col2 varchar(100))
insert @t2
select 'Relation1' UNION
select 'Relation2' UNION
select 'Relation3' UNION
select 'Relation4' UNION
select 'Relation5'UNION
select 'Relation6'
;with cteAllColumns as (
select col1 as col
from @t1
union
select col2 as col
from @t2
)
insert into #pk
select c1.col , c2.col
from cteAllColumns c1
cross join cteAllColumns c2
where c1.col < c2.col
order by c1.col
select * from #pk
--option 2
--Create table #pk(col1 varchar(4000),col2 varchar(4000),primary key(col1,col2))
DECLARE @ConcatString1 VARCHAR(4000)
SELECT top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM master.dbo.sysdatabases
DECLARE @ConcatString2 VARCHAR(4000)
DECLARE @tags NVARCHAR(4000) = 'Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9,Data10,Data11'
SELECT @ConcatString2 = COALESCE(@ConcatString2 + ', ', '') +value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';
Create table #CSV1(Data1 VARCHAR(4000))
Insert into #CSV1
Values(@ConcatString1)
Create table #CSV2(Data2 VARCHAR(4000))
Insert into #CSV2
Values(@ConcatString2)
select * from #CSV1
select * from #CSV2
--drop table #CSV1
--drop table #CSV2
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data1, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV1) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C2
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data2, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV2) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
Select * from #C1
Select * from #C2
declare @t1 table (Info1 varchar(8000),col1 varchar(8000))
insert into @t1
Select * FROM #CSV1
CROSS APPLY STRING_SPLIT(Data1, ',');
declare @t2 table (Info2 varchar(8000),col2 varchar(8000))
insert into @t2
Select * FROM #CSV2
CROSS APPLY STRING_SPLIT(Data2, ',');
Select * from @t1
Select * from @t2
;with cteAllColumns as (
select col1 as col
from @t1
union
select col2 as col
from @t2
)
select c1.col, c2.col
from cteAllColumns c1
cross join cteAllColumns c2
where c1.col < c2.col
order by c1.col
--select * from #pk
drop table #CSV1
drop table #CSV2
--drop table #pk
drop table #C1
drop table #C2
--option 3
DECLARE @ConcatString VARCHAR(4000)
SELECT top 11 @ConcatString = COALESCE(@ConcatString + ', ', '') + name
FROM master.dbo.sysdatabases
DECLARE @ConcatString1 VARCHAR(4000)
SELECT top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM ts4.master.dbo.sysdatabases
where name not in ('Data1', 'Data2', 'Data3', 'Data4', 'Data5', 'Data6', 'Data7', 'Data8', 'Data9', 'Data10', 'Data11')
Create table #CSV(Data VARCHAR(4000))
Insert into #CSV
Values(@ConcatString)
Insert into #CSV
Values(@ConcatString1)
select * from #CSV
--drop table #CSV
--select parsename(Data,4)as [4],parsename(Data,3)as [3],parsename(Data,2)as [2],parsename(Data,1)as [1] from
--(Select replace(Data,',','.') Data from #CSV) t
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name5
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name5
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
drop table #CSV
------------------------------------Stored procedure used jobs-------------------------------
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sJSTP].[step_uid] AS [StepID]
, [sJSTP].[step_id] AS [StepNo]
, [sJSTP].[step_name] AS [StepName]
, CASE [sJSTP].[subsystem]
WHEN 'ActiveScripting' THEN 'ActiveX Script'
WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
WHEN 'PowerShell' THEN 'PowerShell'
WHEN 'Distribution' THEN 'Replication Distributor'
WHEN 'Merge' THEN 'Replication Merge'
WHEN 'QueueReader' THEN 'Replication Queue Reader'
WHEN 'Snapshot' THEN 'Replication Snapshot'
WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
ELSE sJSTP.subsystem
END AS [StepType]
, [sPROX].[name] AS [RunAs]
, [sJSTP].[database_name] AS [Database]
, [sJSTP].[command] AS [ExecutableCommand]
, CASE [sJSTP].[on_success_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
+ ' '
+ [sOSSTP].[step_name]
END AS [OnSuccessAction]
, [sJSTP].[retry_attempts] AS [RetryAttempts]
, [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
, CASE [sJSTP].[on_fail_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
+ ' '
+ [sOFSTP].[step_name]
END AS [OnFailureAction]
FROM
[msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
ON [sJSTP].[job_id] = [sJOB].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
ON [sJSTP].[job_id] = [sOSSTP].[job_id]
AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
ON [sJSTP].[job_id] = [sOFSTP].[job_id]
AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
WHERE [sJSTP].[command] LIKE '%Pr_PreEmploymentAct%'
ORDER BY [JobName], [StepNo]
--------------------------------------------------------super query-------------------
declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=4
set @numrows3=2
set @numrows4=4
create table #c1(Id int,Name varchar(300),Value int)
declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' * from t11 Order by Id'
insert into #c1
Execute (@vSQL)
update #c1 set Name=Name+'('+'WK'+')'
create table #c2(Id int,Name varchar(300),Value int)
declare @vSQL2 varchar(1000)
--select @numrows2 = 4
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' * from t12 Order by Id'
insert into #c2
Execute (@vSQL2)
update #c2 set Name=Name+'('+'BT'+')'
create table #c3(Id int,Name varchar(300),Value int)
declare @vSQL3 varchar(1000)
--select @numrows2 = 4
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' * from t13 Order by Id'
insert into #c3
Execute (@vSQL3)
update #c3 set Name=Name+'('+'ALL'+')'
create table #c4(Id int,Name varchar(300),Value int)
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' * from t14 Order by Id'
insert into #c4
Execute (@vSQL4)
update #c4 set Name=Name+'('+'BL'+')'
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select *
into #fmtb4
from cteAllColumns cte1
order by col desc
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select cte1.col col1,cte2.col col2
into #fmtbl
from cteAllColumns cte1
cross join cteAllColumns cte2
where cte1.col < cte2.col
order by cte1.col
Declare @Column1 Nvarchar(300)
Declare @Column2 Nvarchar(300)
Declare @Column1After Nvarchar(300)
Declare @Column2After Nvarchar(300)
DECLARE CC CURSOR FOR
SELECT DISTINCT col1,col2
FROM #fmtbl
OPEN CC
FETCH NEXT FROM CC INTO @Column1,@Column2
WHILE @@FETCH_STATUS = 0
BEGIN
set @Column1After=@Column1+'['+'C'+']'
set @Column2After=@Column2+'['+'VC'+']'
update #fmtbl set col1=@Column1After where col1=@Column1 and col1<>@Column1+'['+'C'+']'
update #fmtbl set col2=@Column2After where col2=@Column2 and col1<>@Column2+'['+'VC'+']'
FETCH NEXT FROM CC INTO @Column1,@Column2
END
--select ''[afterfmtb4],* from #fmtbl
--order by col1,col2
SELECT Distinct top 2
stuff(
(
SELECT ','+ col FROM #fmtb4 FOR XML PATH('')
),1,1,'') Results
into #commatbl
FROM (SELECT DISTINCT top 1 col FROM #fmtb4 ) #fmtb4
CLOSE CC
DEALLOCATE CC
select col1,col2,Results
Into #CP
FROM #fmtbl P CROSS Join
#commatbl C
Select ''BEFORE,* from #CP
--update #CP set Results = replace(Results,substring(Results,charindex(col1,(substring(Results,charindex(col1,(Results)),charindex(',',Results))+'['+'C'+']')),charindex(',',Results)-1),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col1,charindex('[',col1),len(right(col1,3)),''),(Results)),len(Stuff(col1,charindex('[',col1),len(right(col1,3)),'')),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col2,charindex('[',col2),len(right(col2,4)),''),(Results)),len(Stuff(col2,charindex('[',col2),len(right(col2,4)),'')),col2)
Select 'AFTER'AFTER,* from #CP
Order by col1,col2
SELECT DISTINCT col1,col2,
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
--into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CP) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl
------------------------------------------Nth occurence position---------------------------------------------
create table #test
( t varchar(50) );
insert into #test values
( 'abc_1_2_3_4.gif'),
('zzz_12_3_3_45.gif');
select t,
charindex( '_', t ) as Occurence_first,
charindex( '_', t, charindex( '_', t ) + 1 ) as Occurence_second from #test
drop table #test
https://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string
------------------------------------------------To know db size and free space info----------------------------------------
Select DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
(size * 8.0/1024) as Size,
((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
From sys.database_files
------------------------------------------------To know total transactions per second or minute---------------------------------------------
Create table #monitor(servername varchar(30),Totaltransactions int,AvgTransactionPerSecond int)
---First PASS
DECLARE @First BIGINT
DECLARE @Second BIGINT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%Databases%'
AND counter_name like '%Transactions/sec%'
AND instance_name like '%_Total%';
-- Following is the delay
WAITFOR DELAY '00:01:00'
--Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%Databases%'
AND counter_name like '%Transactions/sec%'
AND instance_name like '%_Total%';
Insert into #monitor
SELECT @@SERVERNAME AS SERVERNAME,(@Second - @First) 'Total Transactions', ((@Second - @First)/60) 'Avg Transactions Per Second'
Select * from #monitor
drop table #monitor
-------------------------------------Comma with sum------------------------
Declare @t table(Sno int identity(1,1),Name nvarchar(200),Score int,servers varchar(100))
insert into @t
Select 'master',15,'TS3'
union
Select 'master',85,'TS4'
union
Select 'master',0,'TPCDB'
union
Select 'master',31,'SHELL1P1'
union
Select 'tempdb',10,'TS3'
union
Select 'tempdb',0,'TS4'
union
Select 'tempdb',3,Null
union
Select 'tempdb',4,'SHELL1P1'
select * from @t
SELECT Name,
stuff(
(
SELECT ','+ CAST(Score AS varchar) + '('+ISNULL(servers,'DNP')+')'
FROM @t WHERE Name = t.Name
order by Sno desc
FOR XML PATH('')
),1,1,'') resultswithservers
Into #comma
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name
SELECT Name,
stuff(
(
SELECT ','+ CAST(Score AS varchar)
FROM @t WHERE Name = t.Name
order by Sno desc
FOR XML PATH('')
),1,1,'') results
into #ct
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name
;WITH ValList AS(
SELECT Name,
CAST(LEFT(results,PATINDEX('%,%', results) - 1) AS INT) Val,
RIGHT(results,LEN(results) - PATINDEX('%,%', results)) Remainder,results
FROM #ct
UNION ALL
SELECT Name,
CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS INT) Val,
RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder,results
FROM ValList
WHERE LEN(Remainder) > 0
)
SELECT V.Name,results,resultswithservers,
SUM(Val)[Sumofcomma],AVG(Val) [AVG]
FROM ValList V
join #comma C On V.Name=C.Name
GROUP BY V.Name,results,resultswithservers
--CREATE FUNCTION ufn_sum_csv(@string varchar(100))
--RETURNS @result
--Declare @SQL NVARCHAR(500)= N'SELECT @result_out = ' + REPLACE(@string, ',', '+')
--EXECUTE sp_executesql @SQL, N'@result_out FLOAT OUTPUT', @result_out = @result OUTPUT
--SELECT @result
--RETURN
drop table #ct
drop table #comma
http://www.kodyaz.com/t-sql/create-date-time-intervals-table-in-sql-server.aspx
--------------------------Change job owner name-----------------------------------------------------
DECLARE @name_holder VARCHAR(1000)
DECLARE My_Cursor CURSOR
FOR
SELECT sj.name
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.syscategories ON sj.category_id = msdb.dbo.syscategories.category_id
left join master.sys.syslogins l on sj.owner_sid = l.sid
WHERE syscategories.name <> 'Report Server'
and l.name not in ('sa','Administrator')
OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @name_holder
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec msdb..sp_update_job
@job_name = @name_holder,
@owner_login_name = 'sa'
FETCH NEXT FROM My_Cursor INTO @name_holder
END
CLOSE My_Cursor
DEALLOCATE My_Cursor
-------------------------------------------------------Job Name with Owner name-------------------------------------
SELECT @@servername as servername,sj.name 'Job Name',
syscategories.name 'Category',
CASE [description]
WHEN 'No Description available.' THEN ''
ELSE [description]
END AS 'Description'
,case when sj.enabled=1 then 'Yes' when sj.enabled=0 then 'No' end as Job_Enabled_status,owner_sid,l.name Job_OwnerName,sj.date_created,sj.date_modified
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.syscategories ON sj.category_id = msdb.dbo.syscategories.category_id
left join master.sys.syslogins l on sj.owner_sid = l.sid
WHERE syscategories.name <> 'Report Server'
and l.name not in ('sa','TEXMO\Administrator','rep','dbadmin')
ORDER BY sj.name
--select owner_sid,* from msdb.dbo.sysjobs
--WHERE enabled= 1
--------------------------------------------To know sysadmin of logins---------------------------------------------
USE master
GO
SELECT @@servername as ServerName,p.name AS [loginname] ,
p.type ,
p.type_desc ,
p.is_disabled,
CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%'
-- Logins that are sysadmins
AND s.sysadmin = 1
order by loginname
------------------------------------------------To take Backup of remote server via linked server-------------
DECLARE @DynamicSQL NVARCHAR(4000)
SET @DynamicSQL = 'BACKUP DATABASE Teis_scs TO DISK = ''c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Teis_scs.bak'''
EXECUTE [P1SYS].master.dbo.sp_executesql @DynamicSQL
------------------------------------------------------------------To take Backup of remote server via linked server and remove 3 days retentions--------------
DECLARE @DeleteDate DATETIME =DATEADD(dd,-3,GETDATE()); -- Cutoff date
select @DeleteDate
EXEC [P1SYS].master.sys.xp_delete_file 0,'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\','BAK',@DeleteDate,0;
DECLARE @DynamicSQL NVARCHAR(4000)
DECLARE @name VARCHAR(50)
DECLARE @fileName VARCHAR(500)
DECLARE @fileDate VARCHAR(20)
select @name='Teis_scs'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SET @fileName = @name + '_' + @fileDate + '.bak';
select @fileName
SET @DynamicSQL = 'BACKUP DATABASE Teis_scs TO DISK = ''c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\'+@fileName+''''
select @DynamicSQL
EXECUTE [P1SYS].master.dbo.sp_executesql @DynamicSQL
--------------------------------------------------With replace option to take Backup of remote server via linked server-------------------------------------------------------------------
DECLARE @DeleteDate DATETIME =DATEADD(dd,-1,GETDATE()); -- Cutoff date
select @DeleteDate
EXEC [P1SYS].master.sys.xp_delete_file 0,'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\','BAK',@DeleteDate,0;
DECLARE @DynamicSQL NVARCHAR(4000)
DECLARE @name VARCHAR(50)
DECLARE @fileName VARCHAR(500)
DECLARE @fileDate VARCHAR(20)
select @name='Teis_scs'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SET @fileName = @name + '_' + @fileDate + '.bak';
select @fileName
SET @DynamicSQL = 'BACKUP DATABASE Teis_scs TO DISK = ''c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\'+@fileName+''''+space(1)+' WITH INIT'
select @DynamicSQL
EXECUTE [P1SYS].master.dbo.sp_executesql @DynamicSQL
-------------------------------------------------------Insert via linked server when SQL Agent is not available like SQL Express 2012---------------------------------
Insert into [LinkedServerName].[dbname].dbo.DBGrowth
EXEC [LinkedServerName].dbname.sys.sp_executesql N'SELECT DB_NAME(database_id),
sum(size/128.0) [Size in MB],sum(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)[Free Space in MB],getdate() Dateofobservation
FROM master.sys.master_files
where DB_NAME(database_id)=db_name()
group by DB_NAME(database_id)'
------------------------------SQL job used objects list-----------------------------------------------
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sJSTP].[step_uid] AS [StepID]
, [sJSTP].[step_id] AS [StepNo]
, [sJSTP].[step_name] AS [StepName]
, CASE [sJSTP].[subsystem]
WHEN 'ActiveScripting' THEN 'ActiveX Script'
WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
WHEN 'PowerShell' THEN 'PowerShell'
WHEN 'Distribution' THEN 'Replication Distributor'
WHEN 'Merge' THEN 'Replication Merge'
WHEN 'QueueReader' THEN 'Replication Queue Reader'
WHEN 'Snapshot' THEN 'Replication Snapshot'
WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
ELSE sJSTP.subsystem
END AS [StepType]
, [sPROX].[name] AS [RunAs]
, [sJSTP].[database_name] AS [Database]
, [sJSTP].[command] AS [ExecutableCommand]
, CASE [sJSTP].[on_success_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
+ ' '
+ [sOSSTP].[step_name]
END AS [OnSuccessAction]
, [sJSTP].[retry_attempts] AS [RetryAttempts]
, [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
, CASE [sJSTP].[on_fail_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
+ ' '
+ [sOFSTP].[step_name]
END AS [OnFailureAction]
FROM
[msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
ON [sJSTP].[job_id] = [sJOB].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
ON [sJSTP].[job_id] = [sOSSTP].[job_id]
AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
ON [sJSTP].[job_id] = [sOFSTP].[job_id]
AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
WHERE [sJSTP].[command] LIKE '%Pr_Monitor_Health_Status_of_all_servers%'
ORDER BY [JobName], [StepNo]
---------------------------------DB growth------------------------------------
IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T (DBName nvarchar(500),SizeinMB float,FreeSpaceinMB float,Dateofobservation datetime)
GO
INSERT INTO #T
EXEC sp_MSforeachdb 'USE ? SELECT ''?'' DBName,
sum(size/128.0) [Size in MB],sum(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)[Free Space in MB],getdate() Dateofobservation
FROM master.sys.master_files
where DB_NAME(database_id)=db_name()
group by DB_NAME(database_id)'
GO
Insert into [db_admin].dbo.DBGrowth
SELECT DBName,SizeinMB TotalSizeinMB,FreeSpaceinMB,(SizeinMB -FreeSpaceinMB) [OccupiedinMB], Round((SizeinMB -FreeSpaceinMB)/1024,2) [OccupiedinGB],Dateofobservation
FROM #T
where DBName not in ('dbname')
ORDER BY DBName
----------------------------------------------------object find-----------
--exec prfind 't','olap'
CREATE proc [dbo].[prfind] (@ptname varchar(1),@fbname varchar(50) = '')
as
begin
/*---------------HELP------------------------------------------------------------------------------------------------------------------------
--Use : To find the T - Tables, V- Views , P - Procedures , F - Functions , S - Search All texts , J - Search in JOBs , C - Column Names
--Author : BABU SRINIVASAN.P - PBN
--Creation Date on 04/05/06
--Parameter 1. @ptname - for type of object wise search
--Parameter 2. @fbname - search for given word or letter
--Parameter @ptname t- for Tables, p - for Procedures , v for Views , f for Functions,c for column name,s search the word in all objects,l(L) search and locate the old and new word
--Parameter @fbname any part of the search object name
---------------------------------------------------------------------------------------------------------------------------------------------*/
if upper(@ptname)='T' and upper(@fbname)<>''
begin
select name from sys.tables where upper(name) like '%'+upper(@fbname)+'%' order by create_date desc
end
else if upper(@ptname)='T' and upper(@fbname)=''
begin
select name from sys.tables order by create_date desc
end
else if upper(@ptname)='P' and upper(@fbname)<>''
begin
select name from sys.procedures where upper(name) like '%'+upper(@fbname)+'%' order by create_date desc
end
else if upper(@ptname)='P' and upper(@fbname)=''
begin
select name from sys.procedures order by create_date desc
end
else if upper(@ptname)='V' and upper(@fbname)<>''
begin
select name from sys.views where upper(name) like '%'+upper(@fbname)+'%' order by create_date desc
end
else if upper(@ptname)='V' and upper(@fbname)=''
begin
select name from sys.views order by create_date desc
end
else if upper(@ptname)='F' and upper(@fbname)<>''
begin
select name from sys.sysobjects where upper(xtype) in('TF','FN') and upper(name) like '%'+upper(@fbname)+'%' order by crdate desc
end
else if upper(@ptname)='F' and upper(@fbname)=''
begin
select name from sys.sysobjects where upper(xtype) in('TF','FN') order by crdate desc
end
else if upper(@ptname)='S'
begin
select b.type_desc,name=OBJECT_NAME(a.object_id) from sys.sql_modules a inner join sys.objects b on a.object_id=b.object_id
where UPPER(definition) like '%'+ upper(@fbname) +'%'
union
select distinct type='SQL_JOBS',b.name from msdb.dbo.sysjobsteps a inner join msdb.dbo.sysjobs b on a.job_id=b.job_id
where UPPER(command) like '%'+ upper(@fbname) +'%'
end
else if upper(@ptname)='J'
begin
select distinct b.name from msdb.dbo.sysjobsteps a inner join msdb.dbo.sysjobs b on a.job_id=b.job_id
where UPPER(b.name) like '%'+ upper(@fbname) +'%'
end
else if upper(@ptname)='C'
begin
select distinct Object_Type=b.type_desc,Object_Name=b.name,Column_Name=a.name from sys.columns a inner join sys.objects b on a.object_id=b.object_id
where upper(a.name) like '%'+ upper(@fbname) +'%'
order by b.name
end
else if upper(@ptname)='L'
begin
declare @s1 varchar(50),@s2 varchar(50)
set @s1=teis_mms.dbo.getstring(@fbname,0,',')
set @s2=teis_mms.dbo.getstring(@fbname,1,',')
declare @oldloc table (cdesc varchar(500),cname varchar(500))
insert into @oldloc
select b.type_desc,name=OBJECT_NAME(a.object_id) from sys.sql_modules a inner join sys.objects b on a.object_id=b.object_id
where UPPER(definition) like '%'+ upper(@s1) +'%'
union
select distinct type='SQL_JOBS',b.name from msdb.dbo.sysjobsteps a inner join msdb.dbo.sysjobs b on a.job_id=b.job_id
where UPPER(command) like '%'+ upper(@s1) +'%'
declare @newloc table (cdesc varchar(500),cname varchar(500))
if @s2<>''
begin
insert into @newloc
select b.type_desc,name=OBJECT_NAME(a.object_id) from sys.sql_modules a inner join sys.objects b on a.object_id=b.object_id
where UPPER(definition) like '%'+ upper(@s2) +'%'
union
select distinct type='SQL_JOBS',b.name from msdb.dbo.sysjobsteps a inner join msdb.dbo.sysjobs b on a.job_id=b.job_id
where UPPER(command) like '%'+ upper(@s2) +'%'
end
select distinct coldlocation_type_desc=a.cdesc,coldlocation_name=a.cname,cnewlocation_type_desc=b.cdesc,cnewlocation_name=b.cname from @oldloc a left join @newloc b on a.cname=b.cname
end
end
---------------------To check table object used objects in all db ----------------------------------------------
EXEC sp_MSforeachdb 'USE ? SELECT ''?'' DBName SELECT Distinct o.name,c.text
FROM syscomments c
JOIN sysobjects o
ON c.id = o.id
JOIN INFORMATION_SCHEMA.TABLES t
ON c.text LIKE ''%tablename%'''
----------------------------------The operating system returned error 21(The device is not ready.) -------------------
When you access database or issue sp_helpdb dbname
you will get below error
Msg 823, Level 24, State 2, Procedure sysfiles, Line 2
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000016f0000 in file 'D:\Data\dbname_data.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Even when you tried to run dbcc checkdb you got below error
Msg 5901, Level 16, State 1, Line 1
One or more recovery units belonging to database 'dbname' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 823, Level 24, State 2, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000116000 in file 'D:\Data\dbname.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
solution
you need to issue below statement
use dbname
checkpoint
then
alter database dbname set online
now
you can issue sp_helpdb will works fine
sp_helpdb dbname
or
apply latest service pack in SQL 2012
https://www.sqlskills.com/blogs/paul/20122014-bug-that-can-cause-database-or-server-to-go-offline/
you can see error log as below
2019-08-21 06:33:59.950
spid62
Setting database option ONLINE to ON for database 'dbname'.
2018-08-21 09:32:23.720
spid33s
0 transactions rolled back in database 'dbname' (5:0). This is an informational message only. No user action is required.
2018-08-21 09:32:23.720
spid33s
Recovery is writing a checkpoint in database 'dbname' (5). This is an informational message only. No user action is required.
2018-08-21 09:32:23.690
spid33s
1 transactions rolled forward in database 'dbname' (5:0). This is an informational message only. No user action is required.
2018-08-21 09:32:23.150
spid33s
Starting up database 'dbname'.
2018-08-21 09:32:23.070
spid33s
Error: 17053, Severity: 16, State: 1.
2018-08-21 09:32:23.070
spid33s
fcb::close-flush: Operating system error (null) encountered.
2018-08-21 06:32:23.070
spid33s
Error: 17053, Severity: 16, State: 1.
2018-08-21 06:32:23.070
spid33s
fcb::close-flush: Operating system error (null) encountered.
2018-08-21 06:32:23.000
spid53
Error: 3314, Severity: 21, State: 5.
2018-08-21 06:32:23.000
spid53
During undoing of a logged operation in database 'dbname', an error occurred at log record ID (18985:421:2). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2018-08-21 06:32:22.950
spid53
Database dbname was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
2018-08-21 06:32:22.890
spid53
Error: 3314, Severity: 16, State: 3.
2018-08-21 06:32:22.890
spid53
During undoing of a logged operation in database 'dbname', an error occurred at log record ID (18985:421:4). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2018-08-21 06:32:22.870
spid53
Error: 9001, Severity: 16, State: 5.
2018-08-21 06:32:22.870
spid53
The log for database 'dbname' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2018-08-21 06:32:22.850
spid53
Error: 9001, Severity: 21, State: 4.
2018-08-21 06:32:22.850
spid53
The log for database 'dbname' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2018-08-21 06:32:22.840
spid1s
Write error during log flush.
2018-08-21 06:32:22.830
spid1s
Error: 17053, Severity: 16, State: 1.
2018-08-21 06:32:22.830
spid1s
SQLServerLogMgr::LogWriter: Operating system error 21(The device is not ready.) encountered.
2019-08-21 06:32:20.100
spid53
Error: 823, Severity: 24, State: 2.
2019-08-21 06:32:20.100
spid53
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000016f0000 in file 'D:\Data\dbname.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2018-08-21 06:32:16.910
spid53
Error: 823, Severity: 24, State: 2.
2018-08-21 06:32:16.910
spid53
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000016f0000 in file 'D:\Data\dbname.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
----------------------------SP/Function/Views code via TSQL-------------------------------------------------------------------
SELECT
DB_NAME() DB_Name,
Name,
ISNULL(smsp.definition, ssmsp.definition) AS [Definition],create_date,modify_date--,*
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE ISNULL(smsp.definition, ssmsp.definition) is not null and name not like '%sp_%' and name not like '%dm_%' and name not like '%xp_%' and name not like '%fn_%' and name not like '%sys%' and
name not like '%xml_%' and ssmsp.definition is null and
(sp.type = N'P' OR sp.type = N'V' OR sp.type='TF')
EXEC sp_MSforeachdb 'USE ? SELECT ''?'' DBName SELECT Name,
ISNULL(smsp.definition, ssmsp.definition) AS [Definition],create_date,modify_date--,*
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE ISNULL(smsp.definition, ssmsp.definition) is not null and name not like ''%sp_%'' and name not like ''%dm_%'' and name not like ''%xp_%'' and name not like ''%fn_%'' and name not like ''%sys%'' and
name not like ''%xml_%'' and ssmsp.definition is null and
(sp.type = N''P'' OR sp.type = N''V'' OR sp.type=''TF'')'
----------------------------------------------Combinations check-------------------
WITH CTE as
( SELECT id,num,id as Grp,0 as parent,
num as CSum,
1 as cnt,
CAST(id as Varchar(MAX)) as path
from T where num<=75
and id not in (5,6,7)
UNION all
SELECT T.id,T.num,
CTE.Grp as Grp,
CTE.id as parent,
T.num+CTE.CSum as CSum,
CTE.cnt+1 as cnt,
CTE.path+','+CAST(T.id as Varchar(MAX)) as path
from T
JOIN CTE on T.num+CTE.CSum<=75
and CTE.id<T.id
where T.id not in (5,6,7)
),
BACK_CTE as
(select CTE.id,CTE.num,CTE.Grp,
CTE.path ,CTE.cnt as cnt,
CTE.parent,CSum
from CTE where CTE.CSum=75
union all
select CTE.id,CTE.num,CTE.Grp,
BACK_CTE.path,BACK_CTE.cnt,
CTE.parent,CTE.CSum
from CTE
JOIN BACK_CTE on CTE.id=BACK_CTE.parent
and CTE.Grp=BACK_CTE.Grp
and BACK_CTE.CSum-BACK_CTE.num=CTE.CSum
WHERE CTE.id not in (5,6,7)
)
select id,num,path, cnt as ItemsCount
into #Temp
from BACK_CTE
where id not in (5,6,7)
and cnt=8
order by cnt,path,id
select *
--into T3
from
(select ROW_NUMBER()over(partition by id,num,path order by id ) row
, * from #Temp
)as a
where row=1
;with cte as
(
select c.splitid, T.id,c.path,T.category
from T
inner join
(
select i.items splitid,T3.id,T3.path
from T3
cross apply dbo.Split(T3.path, ',') i
) c
on T.id = c.splitid
)
select path,category,count(distinct cast(splitid as int))[countofcategory] from cte
group by path,category
order by path,category
select * from T order by id
--select * from T3 order by id
--SELECT path, len(path) - len(replace(path, ',', '')) +1 IndividualCount
--FROM #comma
--select sum(num) s from T where id in (1,2,3,10,11,12,15,16)
--CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
--returns @temptable TABLE (items varchar(MAX))
--as
--begin
-- declare @idx int
-- declare @slice varchar(8000)
-- select @idx = 1
-- if len(@String)<1 or @String is null return
-- while @idx!= 0
-- begin
-- set @idx = charindex(@Delimiter,@String)
-- if @idx!=0
-- set @slice = left(@String,@idx - 1)
-- else
-- set @slice = @String
-- if(len(@slice)>0)
-- insert into @temptable(items) values(@slice)
-- set @String = right(@String,len(@String) - @idx)
-- if len(@String) = 0 break
-- end
--return
--end;
select Distinct A.path,T3.ItemsCount,T.category,Sum(T.num)[Sumofpathnum],Count(T.id)[Countofcategoryid)]
from (
select i.items splitid,T3.id,T3.path
from T3
cross apply dbo.Split(T3.path, ',') i
)as A
join T on A.splitid=T.id
join T3 on T3.path=A.path and T3.id=A.id and T3.id=T.id
Group by A.path,T3.ItemsCount,T.category
Select * from T3
--------------------------------------------------------------------------------------------------
declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=4
set @numrows3=3
set @numrows4=3
create table #c1(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc1(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
INSERT INTO #c1
Select 'MS Dhoni',8.5,'Wicket Keeper'
union all
Select 'Alex Carey',8.0,'Wicket Keeper'
declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' Name,Value,Description from #c1 Order by NEWID()'
insert into #cc1
Execute (@vSQL)
update #cc1 set Name=Name+'('+'WK'+')'
create table #c2(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc2(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL2 varchar(1000)
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' Name,Value,Description from #c2 Order by NEWID()'
INSERT INTO #c2
Select 'Virat Kohli',10.5,'BatsMan'
union all
Select 'Rohit Sharma',10.0,'BatsMan'
union all
Select 'Aaron Finch',9.5,'BatsMan'
union all
Select 'Shihar Dhawan',9.5,'BatsMan'
union all
Select 'D Short',8.5,'BatsMan'
union all
Select 'Rishat Pant',8.5,'BatsMan'
union all
Select 'U Khawaja',8.0,'BatsMan'
union all
select 'Peter Handscomb',8.0,'BatsMan'
insert into #cc2
Execute (@vSQL2)
update #cc2 set Name=Name+'('+'BT'+')'
create table #c3(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc3(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL3 varchar(1000)
INSERT INTO #c3
Select 'Marcus Stoinis',9.5,'All Rounder'
union
Select 'Glenn Maxwell',9.0,'All Rounder'
union
Select 'Krunal Pandya',8.5,'All Rounder'
union
Select 'Vijay Shankar',8.0,'All Rounder'
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' Name,Value,Description from #c3 Order by NEWID() '
INSERT INTO #cc3
Execute (@vSQL3)
update #cc3 set Name=Name+'('+'ALL'+')'
create table #c4(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc4(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' Name,Value,Description from #c4 Order by NEWID()'
insert into #c4
Select 'J Bumrah',9.5,'Bowler'
union
Select 'P Cummins',9.0,'Bowler'
union
Select 'Y Chahal',9.0,'Bowler'
union
Select 'K Richardson',8.5,'Bowler'
union
Select 'N Coulternile',8.5,'Bowler'
union
Select 'A Zampa',8.5,'Bowler'
union
Select 'Umesh Yadav',8.5,'Bowler'
insert into #cc4
Execute (@vSQL4)
update #cc4 set Name=Name+'('+'BL'+')'
Declare @tot float
Select @tot=sum([Sumvalue]) from (
select sum(Value)[Sumvalue] from #cc1
union
select sum(Value)[Sumvalue] from #cc2
union
select sum(Value)[Sumvalue] from #cc3
union
select sum(Value)[Sumvalue] from #cc4
)as B
select A.Id,A.Name,Description,sum(distinct A.Sumvalue) [Sum],@tot Total,100-@tot [Remaining]
from
(
select Id,Name,sum(Value)[Sumvalue],Description from #cc1
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc2
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc3
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc4
group by Id,Name,Description
)as A
group by A.Id,A.Name,A.Description
order by A.Description DESC,A.Id,A.Name DESC
;with cteAllColumns as (
select Name as col
from #cc1
union
select Name as col
from #cc2
union
select Name as col
from #cc3
union
select Name as col
from #cc4
)
select *
into #fmtb4
from cteAllColumns cte1
order by col desc
;with cteAllColumns as (
select Name as col
from #cc1
union
select Name as col
from #cc2
union
select Name as col
from #cc3
union
select Name as col
from #cc4
)
select cte1.col col1,cte2.col col2
into #fmtbl
from cteAllColumns cte1
cross join cteAllColumns cte2
where cte1.col < cte2.col
order by cte1.col
;with cteAllColumns as (
select Name as col
from #cc1
union
select Name as col
from #cc2
union
select Name as col
from #cc3
union
select Name as col
from #cc4
)
insert into #fmtbl
select cte2.col col2,cte1.col col1
from cteAllColumns cte1
cross join cteAllColumns cte2
where cte1.col < cte2.col
Declare @Column1 Nvarchar(300)
Declare @Column2 Nvarchar(300)
Declare @Column1After Nvarchar(300)
Declare @Column2After Nvarchar(300)
DECLARE CC CURSOR FOR
SELECT DISTINCT col1,col2
FROM #fmtbl
OPEN CC
FETCH NEXT FROM CC INTO @Column1,@Column2
WHILE @@FETCH_STATUS = 0
BEGIN
set @Column1After=@Column1+'['+'C'+']'
set @Column2After=@Column2+'['+'VC'+']'
update #fmtbl set col1=@Column1After where col1=@Column1 and col1<>@Column1+'['+'C'+']'
update #fmtbl set col2=@Column2After where col2=@Column2 and col1<>@Column2+'['+'VC'+']'
FETCH NEXT FROM CC INTO @Column1,@Column2
END
--select ''[afterfmtb4],* from #fmtbl
--order by col1,col2
SELECT Distinct top 2
stuff(
(
SELECT ','+ col FROM #fmtb4 FOR XML PATH('')
),1,1,'') Results
into #commatbl
FROM (SELECT DISTINCT top 1 col FROM #fmtb4 ) #fmtb4
CLOSE CC
DEALLOCATE CC
select col1,col2,Results
Into #CP
FROM #fmtbl P CROSS Join
#commatbl C
--Select ''BEFORE,* from #CP
--update #CP set Results = replace(Results,substring(Results,charindex(col1,(substring(Results,charindex(col1,(Results)),charindex(',',Results))+'['+'C'+']')),charindex(',',Results)-1),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col1,charindex('[',col1),len(right(col1,3)),''),(Results)),len(Stuff(col1,charindex('[',col1),len(right(col1,3)),'')),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col2,charindex('[',col2),len(right(col2,4)),''),(Results)),len(Stuff(col2,charindex('[',col2),len(right(col2,4)),'')),col2)
--Select 'AFTER'AFTER,* from #CP
--Order by col1,col2
SELECT DISTINCT col1 Captain,col2 VicCaptain,
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
--into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CP) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #cc1
drop table #cc2
drop table #cc3
drop table #cc4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl
sum with comma
-------------------------------------Comma with sum------------------------
Declare @t table(Sno int identity(1,1),Name nvarchar(200),Score int,servers varchar(100))
insert into @t
Select 'master',15,'TS3'
union
Select 'master',85,'TS4'
union
Select 'master',0,'TPCDB'
union
Select 'master',31,'SHELL1P1'
union
Select 'tempdb',10,'TS3'
union
Select 'tempdb',0,'TS4'
union
Select 'tempdb',3,Null
union
Select 'tempdb',4,'SHELL1P1'
select * from @t
SELECT Name,
stuff(
(
SELECT ','+ CAST(Score AS varchar) + '('+ISNULL(servers,'DNP')+')'
FROM @t WHERE Name = t.Name
order by Sno desc
FOR XML PATH('')
),1,1,'') resultswithservers
Into #comma
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name
SELECT Name,
stuff(
(
SELECT ','+ CAST(Score AS varchar)
FROM @t WHERE Name = t.Name
order by Sno desc
FOR XML PATH('')
),1,1,'') results
into #ct
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name
;WITH ValList AS(
SELECT Name,
CAST(LEFT(results,PATINDEX('%,%', results) - 1) AS INT) Val,
RIGHT(results,LEN(results) - PATINDEX('%,%', results)) Remainder,results
FROM #ct
UNION ALL
SELECT Name,
CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS INT) Val,
RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder,results
FROM ValList
WHERE LEN(Remainder) > 0
)
SELECT V.Name,results,resultswithservers,
SUM(Val)[Sumofcomma],AVG(Val) [AVG]
FROM ValList V
join #comma C On V.Name=C.Name
GROUP BY V.Name,results,resultswithservers
--CREATE FUNCTION ufn_sum_csv(@string varchar(100))
--RETURNS @result
--Declare @SQL NVARCHAR(500)= N'SELECT @result_out = ' + REPLACE(@string, ',', '+')
--EXECUTE sp_executesql @SQL, N'@result_out FLOAT OUTPUT', @result_out = @result OUTPUT
--SELECT @result
--RETURN
drop table #ct
drop table #comma
--------------------------------SMTP fix
if (select count(*)
from msdb.dbo.sysmail_faileditems sf
join msdb.dbo.sysmail_event_log sl on sf.mailitem_id=sl.mailitem_id
where log_date between dateadd(minute, -10, getdate()) and dateadd(minute, 0, getdate())
and event_type='error' and subject='Daily Overall Planning'
)>0
Begin
SELECT 'SMTP Error'
end
else
Begin
Select 'No Error in SMTP Connection'
end
---------------------TRYING------------
declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=4
set @numrows3=3
set @numrows4=3
create table #c1(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc1(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
INSERT INTO #c1
Select 'TS3',8.5,'A'
union all
Select 'TS4',8.0,'B'
declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' Name,Value,Description from #c1 Order by NEWID()'
insert into #cc1
Execute (@vSQL)
update #cc1 set Name=Name+'('+'WK'+')'
create table #c2(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc2(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL2 varchar(1000)
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' Name,Value,Description from #c2 Order by NEWID()'
INSERT INTO #c2
Select 'SHELL1P21',10.5,'A'
union all
Select 'SHELL1P2',10.0,'B'
union all
Select 'SHELL2P1',9.5,'A'
union all
Select 'SHELL2P2',9.5,'B'
union all
Select 'TEKPC2',8.5,'A'
union all
Select 'TMS',8.5,'B'
union all
Select 'WIP',8.0,'A'
union all
select 'MATRIX',8.0,'B'
insert into #cc2
Execute (@vSQL2)
update #cc2 set Name=Name+'('+'BT'+')'
create table #c3(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc3(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL3 varchar(1000)
INSERT INTO #c3
Select 'AR1',9.5,'A'
union
Select 'AR2',9.0,'B'
union
Select 'AR3',8.5,'A'
union
Select 'AR4',8.0,'B'
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' Name,Value,Description from #c3 Order by NEWID() '
INSERT INTO #cc3
Execute (@vSQL3)
update #cc3 set Name=Name+'('+'ALL'+')'
create table #c4(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc4(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' Name,Value,Description from #c4 Order by NEWID()'
insert into #c4
Select 'B1',9.5,'A'
union
Select 'B2',9.0,'B'
union
Select 'B3 ',9.0,'A'
union
Select 'B4',8.5,'B'
union
Select 'B5',8.5,'A'
union
Select 'B6',8.5,'B'
union
Select 'B7',8.5,'A'
insert into #cc4
Execute (@vSQL4)
update #cc4 set Name=Name+'('+'BL'+')'
Declare @tot float
Select @tot=sum([Sumvalue]) from (
select sum(Value)[Sumvalue] from #cc1
union
select sum(Value)[Sumvalue] from #cc2
union
select sum(Value)[Sumvalue] from #cc3
union
select sum(Value)[Sumvalue] from #cc4
)as B
select A.Id,A.Name,Description,sum(distinct A.Sumvalue) [Sum],@tot Total,100-@tot [Remaining]
from
(
select Id,Name,sum(Value)[Sumvalue],Description from #cc1
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc2
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc3
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc4
group by Id,Name,Description
)as A
group by A.Id,A.Name,A.Description
order by A.Description DESC,A.Id,A.Name DESC
;with cteAllColumns as (
select Name as col,Description,Value
from #cc1
union
select Name as col,Description,Value
from #cc2
union
select Name as col,Description,Value
from #cc3
union
select Name as col,Description,Value
from #cc4
)
select *
into #fmtb4
from cteAllColumns cte1
order by col desc
;with cteAllColumns as (
select Name as col,Description,Value
from #cc1
union
select Name as col,Description,Value
from #cc2
union
select Name as col,Description,Value
from #cc3
union
select Name as col,Description,Value
from #cc4
)
select cte1.col col1,cte2.col col2,cte1.Description Description1,cte2.Description as Description2,cte1.Value Value1,cte2.Value Value2
into #fmtbl
from cteAllColumns cte1
cross join cteAllColumns cte2
where cte1.col < cte2.col
order by cte1.col
;with cteAllColumns as (
select Name as col,Description,Value
from #cc1
union
select Name as col,Description,Value
from #cc2
union
select Name as col,Description,Value
from #cc3
union
select Name as col,Description,Value
from #cc4
)
insert into #fmtbl
select cte2.col col2,cte1.col col1,cte2.Description Description2,cte1.Description Description1,cte1.Value Value1,cte2.Value Value2
from cteAllColumns cte1
cross join cteAllColumns cte2
where cte1.col < cte2.col
--select * from #fmtbl
Declare @Column1 Nvarchar(300)
Declare @Column2 Nvarchar(300)
Declare @Column1After Nvarchar(300)
Declare @Column2After Nvarchar(300)
Declare @Val1 Nvarchar(300)
Declare @Val2 Nvarchar(300)
DECLARE CC CURSOR FOR
SELECT DISTINCT col1,col2
FROM #fmtbl
OPEN CC
FETCH NEXT FROM CC INTO @Column1,@Column2
WHILE @@FETCH_STATUS = 0
BEGIN
set @Column1After=@Column1+'['+'C'+']'
set @Column2After=@Column2+'['+'VC'+']'
update #fmtbl set col1=@Column1After where col1=@Column1 and col1<>@Column1+'['+'C'+']'
update #fmtbl set col2=@Column2After where col2=@Column2 and col2<>@Column2+'['+'VC'+']'
FETCH NEXT FROM CC INTO @Column1,@Column2
END
select 'ft',* from #fmtbl
select 'ftb4',* from #fmtb4
update #fmtb4 set col=col+cast(Value as varchar)
select 'After cursor update ftb4',* from #fmtb4
--select ''[afterfmtb4],* from #fmtbl
--order by col1,col2
SELECT Distinct top 2
stuff(
(
SELECT ','+ col FROM #fmtb4 FOR XML PATH('')
),1,1,'') Results
into #commatbl
FROM (SELECT DISTINCT top 1 col FROM #fmtb4 ) #fmtb4
SELECT Distinct top 2
stuff(
(
SELECT ','+ cast(Value as varchar) FROM #fmtb4 FOR XML PATH('')
),1,1,'') Results
into #commatblval
FROM (SELECT DISTINCT top 1 col FROM #fmtb4 ) #fmtb4
CLOSE CC
DEALLOCATE CC
Select '#commatbl',* from #commatbl
Select '#commatblval',* from #commatblval
select col1,col2,Results,Description1,Description2,Value1,Value2
Into #CP
FROM #fmtbl P CROSS Join
#commatbl C
select col1,col2,Results,Description1,Description2,Value1,Value2
Into #CPval
FROM #fmtbl P1 CROSS Join
#commatblval C1
Select '#CP',* from #CP
Select '#CPval',* from #CPval
--update #CP set Results = replace(Results,substring(Results,charindex(col1,(substring(Results,charindex(col1,(Results)),charindex(',',Results))+'['+'C'+']')),charindex(',',Results)-1),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col1,charindex('[',col1),len(right(col1,3)),''),(Results)),len(Stuff(col1,charindex('[',col1),len(right(col1,3)),'')),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col2,charindex('[',col2),len(right(col2,4)),''),(Results)),len(Stuff(col2,charindex('[',col2),len(right(col2,4)),'')),col2)
Select 'AFTER'AFTER,* from #CP
--where Results is null
Order by col1,col2
Select 'AFTER Merge'AfterMerge,cpt.col1,cpt.col2,cpt.Value1,cpt.Value2,cpt.Description1,cpt.Description2,cpt.Results,cptval.Results RCSV
into #finalb4
from #CP cpt
join #CPval cptval on cpt.col1=cptval.col1 and cpt.col2=cptval.col2
Select * from #finalb4
;WITH ValList AS(
SELECT col1,col2,
CAST(LEFT(RCSV,PATINDEX('%,%', RCSV) - 1) AS FLOAT) Val,
RIGHT(RCSV,LEN(RCSV) - PATINDEX('%,%', RCSV)) Remainder,RCSV
FROM #finalb4
UNION ALL
SELECT col1,col2,
CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS FLOAT) Val,
RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder,RCSV
FROM ValList
WHERE LEN(Remainder) > 0
)
SELECT V.col1,V.col2,C.RCSV,Results,
SUM(CAST(Val as float))[Sumofcomma],AVG(Val) [AVG]
FROM ValList V
join #finalb4 C On V.col1=C.col1 and V.col2=C.col2
GROUP BY V.col1,V.col2,C.RCSV,Results
--SELECT DISTINCT col1 Captain,col2 VicCaptain,
--S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
--,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
--, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
--, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
--, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
--, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
--,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
--, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
--, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
--, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
--, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
----into #C1
--FROM
--(
--SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
--FROM #CP) d
--CROSS APPLY d.[vals].nodes('/H/r') S(a)
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #cc1
drop table #cc2
drop table #cc3
drop table #cc4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl
drop table #commatblval
drop table #CPval
drop table #finalb4
---------------------------------------------Final with count value---------------
declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=4
set @numrows3=2
set @numrows4=4
create table #c1(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) +' * from test.dbo.V1 Order by newid() '
insert into #c1
Execute (@vSQL)
update #c1 set Name=Name+'('+'WK'+')'
create table #c2(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL2 varchar(1000)
--select @numrows2 = 4
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) +' * from test.dbo.V2 Order by newid() '
insert into #c2
Execute (@vSQL2)
update #c2 set Name=Name+'('+'BT'+')'
create table #c3(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL3 varchar(1000)
--select @numrows2 = 4
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' * from test.dbo.V3 Order by newid() '
insert into #c3
Execute (@vSQL3)
update #c3 set Name=Name+'('+'ALL'+')'
create table #c4(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' * from test.dbo.V4 Order by newid()'
insert into #c4
Execute (@vSQL4)
update #c4 set Name=Name+'('+'BL'+')'
Declare @tot int
Select @tot=sum([Sumvalue]) from (
select sum(Credit)[Sumvalue] from #c1
union
select sum(Credit)[Sumvalue] from #c2
union
select sum(Credit)[Sumvalue] from #c3
union
select sum(Credit)[Sumvalue] from #c4
)as B
Select * into #testingTeamcount from (
select * from #c1
union
select * from #c2
union
select * from #c3
union
select * from #c4
)as B
if @tot<=100
begin
select A.Id,A.Name,A.Team,sum(distinct A.Sumvalue) [Credit],@tot Total
from
(
select Id,Name,Team,sum(Credit)[Sumvalue] from #c1
group by Id,Name,Team
union
select Id,Name,Team,sum(Credit)[Sumvalue] from #c2
group by Id,Name,Team
union
select Id,Name,Team,sum(Credit)[Sumvalue] from #c3
group by Id,Name,Team
union
select Id,Name,Team,sum(Credit)[Sumvalue] from #c4
group by Id,Name,Team
)as A
group by A.Id,A.Name,A.Team
order by A.Id,A.Name,A.Team
select Team,Count(Name)[Sumvalue] from #testingTeamcount
group by Team
if (select Top 1 Count(Name)[Sumvalue] from #testingTeamcount group by Team order by Sumvalue desc)<=7
begin
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select *
into #fmtb4
from cteAllColumns cte1
order by col desc
--select * from #fmtb4
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select cte1.col col1,cte2.col col2
into #fmtbl
from cteAllColumns cte1
cross join cteAllColumns cte2
where cte1.col < cte2.col
order by cte1.col
--select 'fmtbl',* from #fmtbl
Declare @Column1 Nvarchar(300)
Declare @Column2 Nvarchar(300)
Declare @Column1After Nvarchar(300)
Declare @Column2After Nvarchar(300)
DECLARE CC CURSOR FOR
SELECT DISTINCT col1,col2
FROM #fmtbl
OPEN CC
FETCH NEXT FROM CC INTO @Column1,@Column2
WHILE @@FETCH_STATUS = 0
BEGIN
set @Column1After=@Column1+'['+'C'+']'
set @Column2After=@Column2+'['+'VC'+']'
update #fmtbl set col1=@Column1After where col1=@Column1 and col1<>@Column1+'['+'C'+']'
update #fmtbl set col2=@Column2After where col2=@Column2 and col1<>@Column2+'['+'VC'+']'
FETCH NEXT FROM CC INTO @Column1,@Column2
END
--select ''[afterfmtb4],* from #fmtbl
--order by col1,col2
SELECT Distinct top 2
stuff(
(
SELECT ','+ col FROM #fmtb4 FOR XML PATH('')
),1,1,'') Results
into #commatbl
FROM (SELECT DISTINCT top 1 col FROM #fmtb4 ) #fmtb4
CLOSE CC
DEALLOCATE CC
--Select * from #commatbl
select col1,col2,Results
Into #CP
FROM #fmtbl P CROSS Join
#commatbl C
--Select ''BEFORE,* from #CP
--update #CP set Results = replace(Results,substring(Results,charindex(col1,(substring(Results,charindex(col1,(Results)),charindex(',',Results))+'['+'C'+']')),charindex(',',Results)-1),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col1,charindex('[',col1),len(right(col1,3)),''),(Results)),len(Stuff(col1,charindex('[',col1),len(right(col1,3)),'')),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col2,charindex('[',col2),len(right(col2,4)),''),(Results)),len(Stuff(col2,charindex('[',col2),len(right(col2,4)),'')),col2)
--Select 'AFTER'AFTER,* from #CP Order by col1,col2
SELECT DISTINCT col1,col2,
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
, '' Value1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, '' Value2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, '' Value3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, '' Value4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, '' Value5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
, '' Value6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, '' Value7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, '' Value8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, '' Value9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, '' Value10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
, '' Value11
into #FinalwithValue
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CP) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
Select * from #FinalwithValue
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl
drop table #FinalwithValue
drop table #testingTeamcount
end
else
begin
select 'Team count value above 8'
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #testingTeamcount
end
end
else
begin
Select 'Credit Value above 100'
drop table #c1
drop table #c2
drop table #c3
drop table #c4
end
-------------------------------------------------Format with comma check count
--declare @i int =1
--while(@i<50)
--begin
declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=4
set @numrows3=2
set @numrows4=4
create table #c1(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL varchar(8000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) +' * from test.dbo.V1 Order by newid() '
insert into #c1
Execute (@vSQL)
create table #c2(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL2 varchar(8000)
--select @numrows2 = 4
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) +' * from test.dbo.V2 Order by newid() '
insert into #c2
Execute (@vSQL2)
create table #c3(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL3 varchar(8000)
--select @numrows2 = 4
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' * from test.dbo.V3 Order by newid() '
insert into #c3
Execute (@vSQL3)
create table #c4(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL4 varchar(8000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' * from test.dbo.V4 Order by newid()'
insert into #c4
Execute (@vSQL4)
Declare @tot float
Select @tot=sum([Sumvalue]) from (
select sum(Credit)[Sumvalue] from #c1
union
select sum(Credit)[Sumvalue] from #c2
union
select sum(Credit)[Sumvalue] from #c3
union
select sum(Credit)[Sumvalue] from #c4
)as B
create table #Formatcheck(Id int,Name varchar(300),Credit float,Team varchar(30))
insert into #Formatcheck
Select * from #c1
union all
Select * from #c2
union all
Select * from #c3
union all
Select * from #c4
--Select * from #Formatcheck
select Team,Count(Name)[Sumvalue] from #Formatcheck
group by Team
if (select Top 1 Count(Name)[Sumvalue] from #Formatcheck group by Team order by Sumvalue desc)<=7
begin
--Alter table #Formatcheck add Formats char(4)
DECLARE @ConcatString VARCHAR(8000)
SELECT top 11 @ConcatString = COALESCE(@ConcatString + ', ', '') + Name
FROM #Formatcheck
--Select @ConcatString
declare @fmt char(4)
declare @tm char(4)
set @fmt=concat(@numrows,@numrows2,@numrows3,@numrows4)
if exists (select * from formatstructure where Formats=@fmt)
begin
Create table #CSV(Data VARCHAR(8000),Formats char(4),TotalCredit float)
Insert into #CSV(Data,Formats,TotalCredit)
Values(@ConcatString,@fmt,@tot)
select * from #CSV
--create table formatstructure(Formats char(4))
--insert formatstructure
--select 1334
--union all
--select 1325
--union all
--select 1415
--union all
--select 1424
--union all
--select 1433
--union all
--select 1514
--union all
--Select 1523
if @tot<=100
begin
--Insert into Test.dbo.Sampledatacountdup
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11,Formats,@tot TotalCredit
--into Test.dbo.Sampledatacountdup
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
Select 'Record inserted successfully' [Info]
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #Formatcheck
drop table #CSV
end
else
begin
select 'Total Credit value more than 100' [Info]
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #Formatcheck
drop table #CSV
end
end
else
begin
select 'Single Team count existing count 7' [Info]
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #Formatcheck
end
--set @i=@i+1
--end
end
else
begin
select 'No such formats exists' [Info]
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #Formatcheck
end
--------------------------------Final query with val count
declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=5
set @numrows3=2
set @numrows4=3
create table #c1(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) +' * from test.dbo.V1 Order by newid() '
insert into #c1
Execute (@vSQL)
update #c1 set Name=Name+'('+'WK'+')'
create table #c2(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL2 varchar(1000)
--select @numrows2 = 4
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) +' * from test.dbo.V2 Order by newid() '
insert into #c2
Execute (@vSQL2)
update #c2 set Name=Name+'('+'BT'+')'
create table #c3(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL3 varchar(1000)
--select @numrows2 = 4
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' * from test.dbo.V3 Order by newid() '
insert into #c3
Execute (@vSQL3)
update #c3 set Name=Name+'('+'ALL'+')'
create table #c4(Id int,Name varchar(300),Credit float,Team varchar(30))
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' * from test.dbo.V4 Order by newid()'
insert into #c4
Execute (@vSQL4)
update #c4 set Name=Name+'('+'BL'+')'
Declare @tot int
Select @tot=sum([Sumvalue]) from (
select sum(Credit)[Sumvalue] from #c1
union
select sum(Credit)[Sumvalue] from #c2
union
select sum(Credit)[Sumvalue] from #c3
union
select sum(Credit)[Sumvalue] from #c4
)as B
Select * into #testingTeamcount from (
select * from #c1
union
select * from #c2
union
select * from #c3
union
select * from #c4
)as B
if @tot<=100
begin
select A.Id,A.Name,A.Team,sum(distinct A.Sumvalue) [Credit],@tot Total
from
(
select Id,Name,Team,sum(Credit)[Sumvalue] from #c1
group by Id,Name,Team
union
select Id,Name,Team,sum(Credit)[Sumvalue] from #c2
group by Id,Name,Team
union
select Id,Name,Team,sum(Credit)[Sumvalue] from #c3
group by Id,Name,Team
union
select Id,Name,Team,sum(Credit)[Sumvalue] from #c4
group by Id,Name,Team
)as A
group by A.Id,A.Name,A.Team
order by A.Id,A.Name,A.Team
select Team,Count(Name)[Sumvalue] from #testingTeamcount
group by Team
if (select Top 1 Count(Name)[Sumvalue] from #testingTeamcount group by Team order by Sumvalue desc)<=7
begin
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select *
into #fmtb4
from cteAllColumns cte1
order by col desc
--select * from #fmtb4
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select cte1.col col1,cte2.col col2
into #fmtbl
from cteAllColumns cte1
cross join cteAllColumns cte2
where cte1.col < cte2.col
order by cte1.col
--select 'fmtbl',* from #fmtbl
Declare @Column1 Nvarchar(300)
Declare @Column2 Nvarchar(300)
Declare @Column1After Nvarchar(300)
Declare @Column2After Nvarchar(300)
DECLARE CC CURSOR FOR
SELECT DISTINCT col1,col2
FROM #fmtbl
OPEN CC
FETCH NEXT FROM CC INTO @Column1,@Column2
WHILE @@FETCH_STATUS = 0
BEGIN
set @Column1After=@Column1+'['+'C'+']'
set @Column2After=@Column2+'['+'VC'+']'
update #fmtbl set col1=@Column1After where col1=@Column1 and col1<>@Column1+'['+'C'+']'
update #fmtbl set col2=@Column2After where col2=@Column2 and col1<>@Column2+'['+'VC'+']'
FETCH NEXT FROM CC INTO @Column1,@Column2
END
--select ''[afterfmtb4],* from #fmtbl
--order by col1,col2
SELECT Distinct top 2
stuff(
(
SELECT ','+ col FROM #fmtb4 FOR XML PATH('')
),1,1,'') Results
into #commatbl
FROM (SELECT DISTINCT top 1 col FROM #fmtb4 ) #fmtb4
CLOSE CC
DEALLOCATE CC
--Select * from #commatbl
select col1,col2,Results
Into #CP
FROM #fmtbl P CROSS Join
#commatbl C
--Select ''BEFORE,* from #CP
--update #CP set Results = replace(Results,substring(Results,charindex(col1,(substring(Results,charindex(col1,(Results)),charindex(',',Results))+'['+'C'+']')),charindex(',',Results)-1),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col1,charindex('[',col1),len(right(col1,3)),''),(Results)),len(Stuff(col1,charindex('[',col1),len(right(col1,3)),'')),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col2,charindex('[',col2),len(right(col2,4)),''),(Results)),len(Stuff(col2,charindex('[',col2),len(right(col2,4)),'')),col2)
--Select 'AFTER'AFTER,* from #CP Order by col1,col2
--SELECT DISTINCT col1,col2,
--S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
--, '' Value1
--,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
--, '' Value2
--, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
--, '' Value3
--, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
--, '' Value4
--, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
--, '' Value5
--, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
--, '' Value6
--,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
--, '' Value7
--, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
--, '' Value8
--, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
--, '' Value9
--, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
--, '' Value10
--, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
--, '' Value11
----into #FinalwithValue
--FROM
--(
--SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
--FROM #CP) d
--CROSS APPLY d.[vals].nodes('/H/r') S(a)
--Select * from #FinalwithValue
SELECT DISTINCT col1,col2,
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
--into #FinalwithValue
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CP) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl
--drop table #FinalwithValue
drop table #testingTeamcount
end
else
begin
select 'Team count value above 8'
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #testingTeamcount
end
end
else
begin
Select 'Credit Value above 100'
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #testingTeamcount
end
--------------------------------------------
DECLARE @id [nchar](8)
DECLARE @UN [nchar](100)
DECLARE @EID [nchar](200)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT [ID]
,[UserName]
,[UID]
FROM [dbname].[dbo].[Tablename]
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id,@UN,@EID
WHILE @@FETCH_STATUS = 0
BEGIN
Exec pr_procname @id=@sid,@Maild=@EID,@username=@UN
FETCH NEXT FROM db_cursor INTO @sid ,@UN,@EID
END
CLOSE db_cursor
DEALLOCATE db_cursor
