— Applicable for SQL 2005 and later versions USE [Your_DB]; SELECT [Scehma] = schema_name(o.schema_id), o.Name, o.type FROM sys.sql_modules m INNER JOIN sys.objects o ON o.object_id = m.object_id WHERE m.definition like ‘%your_text_to_search%’
Tag: query
How to get rows count in EntityFramework without loading contents
Query syntax: var count = (from t in context.MyTable where t.Id == @Id select t).Count(); Method syntax: var count = context.MyTable .Where(t => t.Id == @Id) .Count() Both generate the same SQL query.
How to get difference between 2 tables in MSSQL
If you have two tables A and B, both with column C, here are the records, which are present in the table A but not in B: SELECT A.* FROM A LEFT JOIN B ON (A.C = B.C) WHERE B.C IS NULL To get all the differences with a single query, a full join must be used, like this: […]
What is the difference between IQueryable and IEnumerable?
That StackOverflow answer so far is the best one I have ever seen: This is an excellent video on youtube which demonstrates how these interfaces differ, worth a watch. Below goes a long descriptive answer to it. The first important point to remember is IQueryable interface inherits from IEnumerable, so whatever IEnumerable can do, IQueryable can also do. There are many differences […]
How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()
In SQL Server 2012+, you can use OFFSET…FETCH. The below query will help you to get 2nd row. But with little changes, you can get the ‘n’th row as well. SELECT <column(s)> FROM <table(s)> ORDER BY <sort column(s)> OFFSET 1 ROWS — Skip this number of rows FETCH NEXT 1 ROWS ONLY; — Return this […]
How to check if recursive triggers are enabled in SQL
Recursive triggers are set at the database level. It’s part of the database metadata information and is available through “sys.databases” view. You can use the below query to check whether the recursive triggers are enabled or not on your database: SELECT name AS ‘Database’, is_recursive_triggers_on AS ‘Recursive Trigger Enabled’ FROM sys.databases Here is the output:
How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL
CHECKPOINT GO –Cleaen Query Cache DBCC DROPCLEANBUFFERS; GO –Clean Execution Plan Cache DBCC FREEPROCCACHE; GO
Create, Alter, Drop and Execute SQL Server Stored Procedures
A stored procedure is a saved block of T-SQL code, such as a query to list the rows in a table. A block of T-SQL code can be saved in a T-SQL script file. You can also store the code from a script file in a stored procedure. There are several benefits that result from […]
SQL Query to Find and Replace text in a stored procedures
Declare @spnames CURSOR Declare @spname nvarchar(max) Declare @moddef nvarchar(max) Set @spnames = CURSOR FOR select distinct object_name(c.id) from syscomments c, sysobjects o where c.text like ‘%findtext%’ and c.id = o.id and o.type = ‘P’ OPEN @spnames FETCH NEXT FROM @spnames into @spname WHILE @@FETCH_STATUS = 0 BEGIN Set @moddef = (SELECT Replace ((REPLACE(definition,’findtext’,’replacetext’)),’ALTER’,’create’) FROM sys.sql_modules […]
“ORDER BY” with CASE and UNION in SQL Server
If you want to use the combination of ORDER BY, CASE and UNION then you should use inner query for a solution without any headache: SELECT * FROM ( SELECT Col_a, Col_b, 0 AS Col_c FROM table1 WHERE conditions UNION SELECT Col_a, NULL AS Col_b, Col_c FROM table2 WHERE conditions ) x ORDER BY CASE […]
Average of all values in a column that are not zero in SQL
SELECT AVG (CASE WHEN Value <> 0 THEN Value ELSE NULL END) …. AVG function will not take into account NULL values. So you can also use this AVG (NULLIF(Value, 0))
Date and Time Conversions in SQL Server
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, […]
How to find records based on CAPITAL and small letters in SQL Query?
SELECT * FROM SQLTABLE WHERE SUBSTRING(TableColumn, 1, 1) = ‘x’ COLLATE SQL_Latin1_General_CP1_CS_AS
Get a Tree view with SQL Query
First of all, I must thank to Maulik Dhorajia for his great post. That post saved me a lot. That’s the @Company table we will use: And here is the SQL Query we need to use: — Working Example ;WITH CTECompany AS ( SELECT EmpID, ParentID, PersonName , 0 AS HLevel, CAST(RIGHT(REPLICATE(‘_’,5) + CONVERT(VARCHAR(20),EmpID),20) AS […]