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
 

Star works In SQL[Use of while loop]

Code	Output
DECLARE @count INT DECLARE @ct INT DECLARE @print varchar(10) SELECT @count =10 WHILE @count > 0 BEGIN SELECT @print= '' SELECT @ct = @count WHILE @ct > 0 BEGIN SELECT @print = @print + '*' SELECT @ct = @ct -1 END PRINT @print SELECT @count=@count-1 END	********** ********* ******** ******* ****** ***** **** *** ** *
DECLARE @count INT DECLARE @ct INT DECLARE @print varchar(10) SELECT @count = 0 WHILE @count < print=" ''" ct =" 0" print =" @print" ct =" @ct" count="@count+1">	* ** *** **** ***** ****** ******* ******** ********* **********

Adding a column with row numbers to the sql Table

THE SQL Code is as follows for without column No field:

SELECT ALSubject FROM Subject_Table


SUBJECTS
Accountancy
Applied Maths
Buniness Studies
Economics
Statistics
Zoology


*********************************************************
The code sql code with adding a column
method:-1
SELECT Row_Number() OVER (order by ALSubject asc) as ID,t.ALSubject As Name FROM ( SELECT ALSubject FROM Subject_Table) t
method:-1
SELECT Row_Number() OVER (order by ALSubject asc) as ID , ALSubject AS Name FROM Subject_Table where ALSubject<>'' group by ALSubject


ID NAME
1 Accountancy
2 Applied Maths
3 Business Studies
4 Economics
5 Statistics
6 Zoology

Check 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

How to analyze without using SQL CURSORs

##First study how to handle data using a CURSOR ##

--Code Start
DECLARE get_Details CURSOR FOR

CREATE TABLE #Temp(Stu_id bigint,
Stu_No nvarchar,
Stu_Name nvarchar)

OPEN get_Details

DECLARE @id bigint
DECLARE @Stu_No nvarchar(64)
DECLARE @Stu_Name nvarchar(100)

FETCH NEXT FROM get_Details INTO @Stu_NO

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @id =id,@Stu_NO = NO,@Stu_Name = firstname,FROM aca_User WHERE id = @Stu_NO

INSERT INTO #Temp ( Stu_id,Stu_NO,Stu_Name) VALUES (@id,@Stu_NO,@Stu_Name)
FETCH NEXT FROM get_Details INTO @Stu_NO END

CLOSE get_Details
DEALLOCATE get_Details

SELECT * FROM #Temp


--Code is End

##Then know about that the same thing can do without using sql Cursor's
Because the cursor want higher memory usage of the RAM & then it produced more lower performance##

drop table EMPLOYEE2
SELECT ROWID=IDENTITY(int,1,1),userid
INTO EMPLOYEE2 FROM aca_role_assignments WHERE (roleid = @roleid) AND (contextid = @contexid) ORDER BY id

declare @rowcount int,
@counter int
SET @rowcount = (SELECT count(userid) FROM aca_role_assignments
WHERE (roleid =@roleid) AND (contextid = @contexid))

CREATE TABLE #Temp( Stu_id bigint,
Stu_NO nvarchar(100), Stu_Name nvarchar(100))

DECLARE @id bigint
DECLARE @Stu_NO nvarchar(100)
DECLARE @Stu_Name nvarchar(100)

set @counter = 1
while @counter <= @rowcount begin SELECT @id =id, @Stu_NO = firstname, @Stu_Name = lastname, from aca_user where id =(SELECT userid FROM EMPLOYEE2 WHERE ROWID=@counter) INSERT INTO #Temp ( Stu_id, Stu_NO,Stu_Name) VALUES (@id, @Stu_NO,@Stu_Name) set @counter = @counter + 1 end DELETE FROM aca_StudentSpecficCourse SELECT * FROM #Temp