Search SQL Server Column Names

Below is a short T-SQL code snippet that demonstrates how to search for some text in all the column names in all the tables in a SQL Server database.

declare @SEARCHTEXT varchar(256)
set @SEARCHTEXT = 'TEXT YOU WANT TO SEARCH FOR'
select t.table_name,
c.column_name,
c.data_type,
c.character_maximum_length
from information_schema.tables t
inner join information_schema.columns c
on c.table_name = t.table_name
where c.column_name like '%' + @SEARCHTEXT + '%'
order by t.table_name, c.column_name

Search SQL Server Stored Procedures and Functions

Below is a short T-SQL code snippet that demonstrates how to search for some text in all the stored procedures and functions in a SQL Server database.

DECLARE @SEARCHTEXT VARCHAR(256)
SET @SEARCHTEXT = 'TEXT YOU WANT TO SEARCH FOR'
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME)) LIKE '%' + @SEARCHTEXT + '%'
ORDER BY ROUTINE_NAME

Query Month to Date, Year to Date, Rolling 12 Months, & Prior Year Sales

Synopsis:

If using Oracle use the trunc and numtoyminterval functions; if using SQL Server use the datediff function.

---

Oracle Month to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE TRANSACTION_DATE BETWEEN trunc(sysdate, 'MONTH') and sysdate

Oracle Year to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE TRANSACTION_DATE BETWEEN trunc(sysdate, 'YEAR') and sysdate

Oracle Rolling 12 Months:

select sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE TRANSACTION_DATE between trunc(sysdate - numtoyminterval(12, 'MONTH'), 'MONTH') and sysdate

Oracle Prior Year:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE trunc(TRANSACTION_DATE, 'YEAR') = trunc(sysdate - numtoyminterval(1, 'YEAR'), 'YEAR')

---

SQL Server Month to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(month, TRANSACTION_DATE, getdate()) = 0
AND TRANSACTION_DATE <= getdate()

SQL Server Year to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(year, TRANSACTION_DATE, getdate()) = 0
AND TRANSACTION_DATE <= getdate()

SQL Server Rolling 12 Months:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(month, TRANSACTION_DATE, getdate()) <= 12
AND TRANSACTION_DATE <= getdate()

SQL Server Prior Year:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(year, TRANSACTION_DATE, getdate()) = 1