Important SQL Queries Every Developer Should Know in SQL Server

Introduction

In this article I am sharing some SQL queries necessary for maintaining data in SQL Server for every developer.SQL


Get the related Stored Procedure to a table


SELECT
DISTINCT o.name, o.xtype
FROM
syscomments c
INNER
JOIN sysobjects o ON c.id=o.id
WHERE
c.TEXT LIKE '%Yourtablename%'

Or you can use following query

SELECT
Name
FROM
sys.procedures
WHERE
OBJECT_DEFINITION(OBJECT_ID) LIKE '%yourtablename%'
 
Get the related table to a Stored Procedure


;
WITH stored_procedures AS (
SELECT

o.name AS proc_name, oo.name AS table_name,

ROW_NUMBER
() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM
sysdepends d
INNER
JOIN sysobjects o ON o.id=d.id
INNER
JOIN sysobjects oo ON oo.id=d.depid
WHERE
o.xtype = 'P')
SELECT
proc_name, table_name FROM stored_procedures
WHERE
row = 1
AND
proc_name LIKE '%YourStoredProcedureName%'
ORDER
BY proc_name,table_name

Get the related trigger to a table


select
so.name, text
from
sysobjects so, syscomments sc
where
type = 'TR'
and
so.id = sc.id and text like '%YourTableName%'

Get the related views to a table


SELECT
view_name, Table_Name
FROM
INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE
Table_Name= 'YourTableName'
ORDER
BY view_name, table_name

Get the related table to a view


SELECT
view_name, Table_Name
FROM
INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE
View_Name = 'YourViewName'
ORDER
BY view_name, table_name

Get the tables that do not have an identity column


SELECT

TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

where

Table_NAME NOT IN

(

SELECT
DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c
INNER
JOIN
sys.identity_columns ic
on

(
c.COLUMN_NAME=ic.NAME))
AND

TABLE_TYPE ='BASE TABLE'
 
Get a list of tables with the number of records of each table


CREATE
TABLE #counts
(

    table_name varchar(255),
    row_count int

)

EXEC
sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?' SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC

If any modifications then please comment.

Thank you.


Similar Articles