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.

Socializer Widget By Blogger Yard

SOCIALIZE IT →

Tweet

FOLLOW US →

SHARE IT →