TSQL customized timestamp

SELECT REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 121), ‘-‘, ”), ‘ ‘, ”), ‘:’, ”), ‘.’, ”)

Advertisements

Determine Monday

In SQL Server, (Monday mod 7) = 0.

-- Microsoft SQL Server 2000
-- Determine Monday

SET DATEFIRST 7
PRINT @@DATEFIRST

-- Method One --------------------------------------------------
DECLARE @value AS INT

SET @value = FLOOR(CAST(GETDATE() AS FLOAT)) + 1
PRINT CAST(@value - (@value % 7) AS DATETIME)

 


-- Method Two --------------------------------------------------
DECLARE @today AS DATETIME
SET @today = GETDATE()
PRINT CAST( FLOOR(CAST(@today AS REAL)) - DATEPART(WEEKDAY, @today) + 2 AS DATETIME)

 


-- Method Three --------------------------------------------------
DECLARE @today AS DATETIME
SET @today = GETDATE()
PRINT CAST( DATEDIFF(DAY, -2, @today) - DATEPART(WEEKDAY, @today) AS DATETIME)

 


-- Method Four --------------------------------------------------

-- This one is not always work.
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE())/7*7, 0)


-- Method Five --------------------------------------------------
SET DATEFIRST 1
SELECT CAST(@date AS DATETIME) - DATEPART(WEEKDAY, CAST(@date AS DATETIME)) + 1

----------------------------------------------------------------
----------------------------------------------------------------

-- determine the first monday of the month
DECLARE @value AS INT
SET @value = FLOOR(CAST(GETDATE() - DATEPART(d, GETDATE()) AS FLOAT)) + 7
SELECT CAST(@value - (@value % 7) AS DATETIME)

record count

-- Microsoft SQL Server 2005 and later
-- record count
SELECT SUM(p.row_count)
FROM sys.schemas s
INNER
JOIN sys.tables t
  ON s.schema_id = t.schema_id
INNER
JOIN sys.dm_db_partition_stats p
  ON t.object_id = p.object_id
WHERE s.name = N'dbo'
  AND t.name = N'table_name'
  AND p.index_id < 2


-- Microsoft SQL Server 2000
-- record count
-- sysindexes is updated in real time
SELECT rows 
FROM sysindexes 
WHERE id = OBJECT_ID('<table>') AND indid < 2

find job name by job command

-- Microsoft SQL Server 2000
-- find job name by job command

select j.name, 
       case j.enabled
           when 1 then 'Yes'
     when 0 then 'No'
       end as Enabled,
       s.step_name,
       s.subsystem,
       s.command

    from msdb..sysjobs j
            inner join
         msdb..sysjobsteps s on j.job_id = s.job_id

    where command like '%mytable%'

Table size

sp_MStablespace
This undocumented system stored procedure returns the number of rows, data page size (KB), and index page size (KB) for general users.

sp_SpaceUsed
This documented system stored procedure will returns the table size information for DBA.

Both stored procedures may return incorrect information because the sysindexes table may not be current.

DBCC UPDATEUSAGE(0) — report and correct space usage statistics for the current database

DBCC CHECKTABLE(‘tablename’) — check the integrity of data

http://sqlserver2000.databases.aspfaq.com/why-does-sp-spaceused-return-inaccurate-values.html