SQL Query Stuff

1.By using this query can search any string contain within stored procedure, function or view

SELECT object_name(id)
FROM sys.syscomments
WHERE text like '%YourFunctionName%'

2.By using this query can search any 'columnName' contain within tables.

SELECT table_name,column_name 
FROM information_schema.columns
WHERE column_name like '%ShiftTypeID%'

3. Case statement within where clause

SELECT * 
FROM ORDERS
WHERE
(
CASE
WHEN @VAR1 = 'Customers' AND CustomerID = @VAR2
THEN 1
WHEN @VAR1 = 'Employee' AND EmployeeID = @VAR2
THEN 1
ELSE 0
END
) = 1
4. Date Comparison in SQL

1. WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
2. WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
3. WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
4. WHERE OrderDate LIKE '01/01/2006%'
5. WHERE OrderDate >= '01/01/2006' AND OrderDate < '01/02/2006'

5. Table name with row counts in sql using "sysobjects "

SELECT [TableName] = so.name,[RowCount] = MAX(si.rows)
FROM sysobjects so,sysindexes si
WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC

6. Get Column name from sql

SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS  c_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%
ORDER BY schema_name, table_name

7. Check the table existence in SQL

IF OBJECT_ID ('DATABASE_NAME.dbo.TABLE_NAME','U') IS NOT NULL
BEGIN
--do any thing if the table exists
ELSE
--do any thing if the table dose not exists
END


'U':- Is a symbol for identifying a table in SQL