How to enter NULL value in SQLServer

• SQLServer 2000

Ctrl+0

• SQLServer 2005

Enter 'NULL'

SQLServer : How to count the length of byte

SQLServer have no funtion to count the length of byte like LENB. This SQL provide same function. *TEST:VARCHAR(25)

DATALENGTH(CONVERT(VARCHAR(25), TEST))

SQLServer : Lock Status

You can see the lock status by sp_lock.

EXEC sp_lock
GO

Type on the result of sp_lock

Type	Description
DB	Database
FIL	File
IDX	Index
PG	Page
KEY	Key
TAB	Table
EXT	Extent
RID	Row ID

SQLServer : How to count the number of records at each table

This is the sample SQL to count the number of records at each table on current database.

SQL:Table for the result

CREATE TABLE [dbo].[TEST](
  [TableName] VARCHAR(255) NOT NULL,
  [NrOfRecords] [int] NOT NULL
) ON [PRIMARY]

SQL:Count the number of records

DECLARE @TABLE_NAME VARCHAR(255)
DECLARE @SQL        VARCHAR(8000)

DECLARE C_TABLE CURSOR FOR 
SELECT 
  name 
FROM 
  sysobjects 
WHERE 
      OBJECTPROPERTY(id, N'IsUserTable') = 1 
  AND status >= 0 
ORDER BY 
  name

OPEN C_TABLE
FETCH NEXT FROM C_TABLE INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM C_TABLE INTO @TABLE_NAME
  PRINT @TABLE_NAME
  SET @SQL = 
      '
        INSERT INTO TEST
        SELECT ''' +
          @TABLE_NAME + ''' AS TableName
          ,COUNT(*) AS NrOfRecords
        FROM ' +
          @TABLE_NAME
      
      EXEC (@SQL)
END

CLOSE C_TABLE

SQLServer : Tables and Columns list

This SQL returns userr tables in current database and their columns.

SELECT
   O.name AS TableName
  ,C.name AS ColumnName
FROM
  sysobjects O
    INNER JOIN syscolumns C
    on 
      O.id = C.ID
WHERE
      OBJECTPROPERTY(O.id, N'IsUserTable') = 1 
  AND O.status >= 0   
ORDER BY
   O.name
  ,C.name

SQLServer : Tables list

This SQL returns the table list on current database.

SELECT 
  name AS TableName
FROM 
  sysobjects 
WHERE 
      OBJECTPROPERTY(id, N'IsUserTable') = 1 
  AND status >= 0 
ORDER BY 
  name

SQLSerer 2000 : Job History

This SQL return the job history of SQLServer Agent in yesterday.

SELECT
  JobHistroy.run_status,
  Jobs.name,
  JobHistroy.step_id,
  JobHistroy.step_name,
  JobHistroy.message,
  JobHistroy.run_date,
  JobHistroy.run_time,
  JobHistroy.server
FROM
  msdb.dbo.sysjobs Jobs

    INNER JOIN
      msdb.dbo.sysjobhistory JobHistroy
      ON
        Jobs.job_id = JobHistroy.job_id
WHERE
  run_date = CAST(
      CAST(YEAR(GETDATE()-1) AS CHAR(4)) +
      RIGHT('00' + CAST(MONTH(GETDATE()-1) AS VARCHAR(2)),2) +
      RIGHT('00' + CAST(DAY(GETDATE()-1) AS VARCHAR(2)),2)
             AS INT) and
  step_id = 0
order by
  jobs.name

SQLServer2000 : Job list

You can get a job list on SQLServer as a table by this SQL.

SELECT TOP 100 PERCENT
  t1.name
 ,t2.step_id
 ,t2.step_name
 ,t2.command
 ,t4.plan_name
 ,t3.enabled
,CAST(LEFT(t3.active_start_date,4) + '/' + SUBSTRING(CAST(t3.active_start_date AS varchar), 5,2) + '/' + RIGHT(t3.active_start_date,2) AS datetime) AS active_start_date
 ,t3.active_start_time
,CAST(LEFT(t3.next_run_date, 4) + '/' + SUBSTRING(CAST(t3.next_run_date AS varchar), 5,2) + '/' + RIGHT(t3.next_run_date,2) AS datetime) AS next_run_date
 ,t3.next_run_time
FROM
 dbo.sysjobs t1
  
  INNER JOIN dbo.sysjobsteps t2
  ON
   t1.job_id = t2.job_id
   
  INNER JOIN dbo.sysjobschedules t3
  ON
   t1.job_id = t3.job_id
   
  LEFT OUTER JOIN dbo.sysdbmaintplans t4
  ON
   SUBSTRING(t2.command,42,36) = CAST(t4.plan_id AS char(36))
ORDER BY
 t1.name