SQL: DB/ Table Queries

SQL Server Management Studio

• How to find a certain string inside a stored procedure/ function

  [sql]SELECT OBJECT_NAME(object_id)
  FROM sys.sql_modules
  WHERE OBJECTPROPERTY(object_id, ‘IsProcedure’) = 1
  AND definition LIKE ‘%keyword%’

  SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
  FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_DEFINITION LIKE ‘%KEYWORD%’ 
  AND ROUTINE_TYPE=’FUNCTION’
  ORDER BY ROUTINE_NAME
  [/sql]

• How to find jobs calling a given Stored Proc?
  Method 1

  [sql]SELECT j.name
  FROM msdb.dbo.sysjobs AS j
  WHERE EXISTS
  (
  SELECT 1 FROM msdb.dbo.sysjobsteps AS s
  WHERE s.job_id = j.job_id
  AND s.command LIKE ‘%store_proc_name%’
  );[/sql]

  Method 2

  [sql]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 ‘%store_proc_name%’
  ORDER BY [JobName], [StepNo][/sql]

• How to find all FK referencing a given table?

  [sql]exec sp_fkeys ‘table_name'[/sql]

• How to find all tables and views having a specified column name?

  [sql]Select * From INFORMATION_SCHEMA.COLUMNS Where column_name = ‘name'[/sql]

  [sql]SELECT t.name AS table_name,
  SCHEMA_NAME(schema_id) AS schema_name,
  c.name AS column_name
  FROM sys.tables AS t
  INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
  WHERE c.name = ‘name’
  ORDER BY schema_name, table_name;[/sql]

• How to find tables & view having 2 column names?

  [sql]SELECT name FROM sysobjects WHERE id IN
  ( SELECT id FROM syscolumns WHERE name = ‘columnA’ )
  and id in
  ( SELECT id FROM syscolumns WHERE name = ‘columnB’ )[/sql]

How to edit a table?

How to add new column?

• SQL Server
  [sql]
  ALTER TABLE [dbo].[RE_RUN]
  ADD [mailsent] bit NULL
  CONSTRAINT [DF_RE_RUN_mailsent] DEFAULT (0)
  [/sql]

How to see table structure by query

[sql]
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ‘[table name]’
[/sql]

SQL Server: How to kill an infinite loop process?

[sql]
— List up all process
select * from sysprocesses (nolock) where blocked = 0 and spid !=@@spid

— Kill process
Kill [spid]
[/sql]

How to add primary key autoincrement

[sql]
ALTER TABLE dbo.YourTable ADD ID INT IDENTITY
[/sql]
then, make it the primary key
[sql]
ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable
PRIMARY KEY(ID)
[/sql]

How to rename column in SQL Server

[sql]
sp_rename ‘table_name.old_column_name’, ‘new_column_name’, ‘COLUMN’;
[/sql]

How to list all foreign keys referencing a given table in SQL Server?

[sql]
EXEC sp_fkeys ‘CUSTOMER’
[/sql]

Check if column exists

[sql]
declare @table_name varchar(256) = ‘TEST_TBL’
declare @update_val varchar(256) = ‘new_value’
declare @sql varchar(max)
set @sql = ‘
IF COL_LENGTH(”dbo.’ + @table_name + ”’, ”column_name”) IS NOT NULL
BEGIN
— Column Exists
UPDATE ‘ + @table_name + ‘
SET column_name = ”’ + @update_val + ”’
END’
exec (@sql)
[/sql]

How to set auto ID for each record

[sql]
DECLARE @LoopCounter INT = 1, @totalRecords INT = 0
SELECT @totalRecords = COUNT(col001) FROM TABLE_NAME

WHILE(@LoopCounter <= @totalRecords) BEGIN UPDATE TOP(1) TABLE_NAME SET col012 = @LoopCounter WHERE col012 is null SET @LoopCounter = @LoopCounter + 1 END [/sql]

How to backup and recover a table data

[sql]
SELECT * INTO TEMP_TBL FROM MAIN_TBL
delete from MAIN_TBL
insert into MAIN_TBL
select * from TEMP_TBL

–note: It’s impossible to delete table if reference FK columns exist
[sql]