1.By using this query can search any string contain within stored procedure, function or view
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
- WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
- WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
- WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
- WHERE OrderDate LIKE '01/01/2006%'
- WHERE OrderDate >= '01/01/2006' AND OrderDate < '01/02/2006'
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
No comments:
Post a Comment