Drop Down list operations

1) Display a text at its index in drop down list.
ddl_idtype.SelectedIndex = ddl_idtype.Items.IndexOf(ddl_idtype.Items.FindByText("String"));

Top 10 Most Used SQL Query

Create a table: We start by creating an empty table by firing the following query:

1. CREATE TABLE student  
2. (  
3.     id INTEGER PRIMARY KEY,  
4.     name varchar(20),  
5.     address varchar(50),  
6.     age INTEGER  
7. );  

This will create a table called “student” with 0 rows in it. This table will store records of students. It will have student id, student name, student address & student age. Its structure will be like this:

Now we will start taking most used 10 queries one by one.

1. Insert Query:
   To insert the records in a table we use Insert Command. The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.
   
   Syntax:
   INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);
   Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data.
   
   You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQL INSERT INTO syntax would be as follows:
   
   Syntax:
   INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
   Example:
   Specify the column names in order after the table name in INSERT INTO statement and then the values you want after VALUES keyword.
   
   
   1. INSERT INTO student (id, name, age) VALUES (‘1’, ‘Nitin’, ‘Noida’, 28);  
   After firing this query, our table will look like:
   
   ID Name Address Age
   
     1     Nitin  Noida     26
   
   We can fire more such queries to fill records in our table:
   
   1. Insert into student (id, name, address, age) values (‘2’, 'Amit', ‘New Delhi ‘23’);  
   2. Insert into student (id, name, address, age) values (‘3’, 'Rohit', ‘Bareilly’ ‘27’);  
   
   So the table now becomes:
   
   ID Name Address Age
   
   1    Nitin Noida       26
   2    Amit New Delhi 23
   3    Rohit Bareilly    27
   
2. Select Query: Viewing all records from a table. These results tables are called result-sets.
   
   Syntax: The basic syntax of SELECT statement is as follows:
   
   SELECT column1, column2, columnN FROM table_name;
   Here, column1, column2...are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax:
   
   SELECT * FROM table_name;
   Example:
   1. SELECT * FROM student;  
   The result of this query will be a display of all rows present in the table.
   
   ID Name Address Age
   
   1 Nitin Noida 26
   2 Amit New Delhi 23
   3 Rohit Bareilly 27
   
   We can also use ORDER BY clause in our select statement to arrange the displayed result in a particular order. For example,
   
   SELECT * FROM student ORDER BY age;
   Result is:
   ID Name Address Age
   
   2 Amit New Delhi 23
   1 Nitin Noida 26
   3 Rohit Bareilly 27
   
   The output is arranged in increasing order of age. We can use DESC keyword after the column name in query if we want to arrange the display in decreasing order.
   
3. Viewing only selected records from a table:
   If there are a huge number of rows in a table and we do not want all the records to fill our display screen, then SQL gives us an option to view only selected rows. Count is useful in counting the number of records.
   
   Syntax: SELECT COUNT(1) FROM table_name;
   Example: SELECT COUNT(1) FROM student;
   The output of this query will be:
   ID Name Address Age 
   
     1 Nitin Noida 26
   
   If we fire: 
   SELECT COUNT * FROM student;
   It will return the number of rows our table has. We can also use MAX & MIN function in our query. For example, if we want to retrieve details of a student with maximum age, we can fire:
   
   SELECT id , name , MAX(age) FROM student;
   We will get:
   ID Name Address Age
   3 Rohit Bareilly 27
   
   We can also check sum of a numeric column. 
   
   For example: SELECT sum(age)FROM student;
   
   It will give 76 as output.
   
   Remember, we can use MAX, MIN, SUM functions only with numeric columns. Using these functions with text column will throw an error.
   
4. Deleting records from a table: To delete the selected rows from a table, just fire the following query,
   
   Syntax: DELETE FROM student WHERE [condition];
   
   Example: DELETE FROM student WHERE name = ‘Rohit’;
   This query will delete the entire row, or more than one row, from table ‘student’ where ‘name’ column have value ‘Rohit’.
   In our case, the result of this query will will look like the following table:
   
   ID Name Address Age
   
   1 Nitin Noida 26
   
   2 Amit New Delhi 23
   
5. Changing data in existing records in a table:
   Suppose we want to change the age of a student named ‘Rohit’ in our table. We would fire this query:
   
   Syntax:The basic syntax of UPDATE query with WHERE clause is as follows:
   
   UPDATE table_nameSET column1 = value1, column2 = value2...., columnN = valueNWHERE [condition];
   Example: 
   UPDATE student SET age = 28 WHERE name = ‘Rohit’;
   You might have observed that we are specifying name in ‘’ where values are characters. This is a must.
   
   Now if we fire:
   SELECT * FROM student;
   We will get the following table as output:
   
   ID Name Address Age
   
   1 Nitin Noida 26
   2 Amit New Delhi 23
   3 Rohit Bareilly 28
   
   Be careful while you are firing UPDATE or DELETE queries with the help of the WHERE clause. Suppose in our table ‘student’ there is more than one student with name ‘Rohit’. In this case, age of all students with the name ‘Rohit’ will be updated to 28. That is why it is always preferred to use PRIMARY KEY in WHERE clause while updating or deleting.
   
   We also need to take care of datatypes in a column while we are changing data in it. A numeric column can have only numbers in it while a text column can have text. This means that if we try to put age = ‘Rohit’ in age column using UPDATE statement, SQL will throw an exception. You can learn more about types of errors and exceptions that occur in SQL. 
   
6. Viewing records from a table without knowing exact details:
   In real life, when we interact with database, there are major chances that we do not know any of the column values exactly. For example, if I am a data operator in a company, I may be aware of the fact that there is an employee called Nitin in our Company as I might have heard other managers talking about him. Now I want to see entire records of Nitin but I am not sure how he spells his name. Whether it is ‘Nitin’ OR ‘Netin’. In this case we can use ‘LIKE’ operator provided by SQL.
   
   We will fire the following query:
   SELECT * FROM student WHERE name LIKE ‘n%n’;
   Output of this query will be:
   ID Name Address Age
   
     1 Nitin Noida 26
   
7. Using more than one condition in WHERE clause to retrieve records:
   To understand the requirement of using this parameter, let us first insert two more rows in our table. Try adding two rows in our table ‘student’ with ID as 4 and 5, name as ‘Suchi’ and age as 22 and 24.
   
   Our table now becomes as:
   ID Name Address Age
   
   1 Nitin Noida 26
   2 Amit New Delhi 23
   3 Rohit Bareilly 27
   4 Suchi Lucknow 22
   5 Suchi Patna 24
   
   Now if we fire our query as:
   SELECT * FROM student WHERE name = ‘suchi’;
   Then output will be:
   ID Name Address Age
   
   4 Suchi Lucknow 22
   5 Suchi Patna 24
   
   So now, we have observed that we were unable to fetch a unique record just by using the name value in WHERE clause. Here arises a need to combine more than one condition in WHERE clause which can be easily done using conditional keywords like AND and OR. For example, if we fire:
   
   SELECT * FROM student WHERE name = ‘suchi’ AND age = 24;
   We get the following output:
   ID Name Address Age
   
     5 Suchi Patna 24
   
   You can also combine AND & OR conditions in WHERE clause to refine your search further more. For example, if we fire
   
   SELECT * FROM student WHERE name = ‘suchi’ OR age > 23
   Output will be:
   ID Name Address Age
   
   1 Nitin Noida 26
   3 Rohit Bareilly 27
   4 Suchi Lucknow 22
   5 Suchi Patna 24
   
   You can use different conditions like AND, OR , <, > in a combination or individually in WHERE clause to fetch the desired rows. 
   
8. Viewing only selected columns from a table:
   If we fire a query like:
   
   SELECT name FROM student WHERE age > 25;
   The following output is displayed:
   Name
   
   Nitin 
   Rohit
   
   We can observe that only names of students are printed. Here we got names of only those students whose age is greater than 25 because of a specified condition in WHERE clause.
   
   We can also use more than one column names in SELECT statement separating them with a,
   
   For example:
   SELECT name, address FROM student;
   Gives this as output:
   Name Address
   
   Nitin Noida 
   Amit New Delhi 
   Rohit Bareilly
   Suchi Lucknow
   Suchi Patna
   
   You can also change the sequence of columns to be displayed on your screen. 
   
   For example:
   SELECT age, name FROM student;
   It will give the following output:
   Age Name
   
   26 Nitin 
   23 Amit
   27 Rohit
   22 Suchi
   24 Suchi
   
9. Know the structure of table:
   It happens with me quite often that I create a table in my database and forget what all columns it has and which column is primary key. With the help of a simple query you can know complete details about the structure of the table you created. Different SQL engines have different commands for it. For example, in SQLite3 the command is:
   
   .schema student;
   • Where as in PostgreSQL it is \d student
   • MySQL uses the following command: describe student;
   • Where ‘student’ is our table’s name.
   
10. Checking performance of query:
    This is an advanced query. It’s particularly useful if you need to figure out why a query is so slow.
    
    Just fire the query:
    EXPLAIN QUERY PLAN SELECT * FROM student;
    This query gives the Query Cost of all operations.
    
    You can use EXPLAIN before a SQL statement to obtain breakup of the timings of the various parts of query. It’s useful for digging up the reason behind a slow query.

Top 50 Important SQL Querry

Query 1: Retrieve List of All Database

1. EXEC sp_helpdb  

Example:

Query 2: Display Text of Stored Procedure, Trigger, View

1. exec sp_helptext @objname = 'Object_Name'  

Example:

Query 3: Get All Stored Procedure Relate To Database

1. SELECT DISTINCT o.name, o.xtype  
2.   
3. FROM syscomments c  
4.   
5. INNER JOIN sysobjects o ON c.id=o.id  
6.   
7. WHERE o.xtype='P'  

Example:

To retrieve the View use “V” instead of “P” and for functions use “FN.

Query 4: Get All Stored Procedure Relate To Table

1. SELECT DISTINCT o.name, o.xtype  
2.   
3. FROM syscomments c  
4.   
5. INNER JOIN sysobjects o ON c.id=o.id  
6.   
7. WHERE c.TEXT LIKE '%Table_Name%' AND o.xtype='P'  

Example:

To retrieve the View use “V” instead of “P” and for functions use “FN.

Query 5: Rebuild All Index of Database

1. EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"  
2.   
3. GO  
4.   
5. EXEC sp_updatestats  
6.   
7. GO  

Example:

Query 6: Retrieve All dependencies of Stored Procedure:

This query return all objects name that are using into stored procedure like table, user define function, another stored procedure.

Query:

1. ;WITH stored_procedures AS (  
2.   
3. SELECT  
4.   
5. oo.name AS table_name,  
6.   
7. ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row  
8.   
9. FROM sysdepends d  
10.   
11. INNER JOIN sysobjects o ON o.id=d.id  
12.   
13. INNER JOIN sysobjects oo ON oo.id=d.depid  
14.   
15. WHERE o.xtype = 'P' AND o.name LIKE '%SP_NAme%' )  
16.   
17. SELECT Table_name FROM stored_procedures  
18.   
19. WHERE row = 1  

Example:

Query 7: Find Byte Size Of All tables in database

1. SELECT sob.name AS Table_Name,  
2.   
3. SUM(sys.length) AS [Size_Table(Bytes)]  
4.   
5. FROM sysobjects sob, syscolumns sys  
6.   
7. WHERE sob.xtype='u' AND sys.id=sob.id  
8.   
9. GROUP BY sob.name  

Example:

Query 8: Get all table that don’t have identity column:

Query:

1. SELECT  
2.   
3. TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
4.   
5. where  
6.   
7. Table_NAME NOT IN  
8.   
9. (  
10.   
11. SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c  
12.   
13. INNER  
14.   
15. JOIN sys.identity_columns ic  
16.   
17. on  
18.   
19. (c.COLUMN_NAME=ic.NAME))  
20.   
21. AND  
22.   
23. TABLE_TYPE ='BASE TABLE'  

Example:

Query 9: List of Primary Key and Foreign Key for Whole Database

1. SELECT  
2.   
3. DISTINCT  
4.   
5. Constraint_Name AS [Constraint],  
6.   
7. Table_Schema AS [Schema],  
8.   
9. Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
10.   
11. GO  

Example:

Query 10: List of Primary Key and Foreign Key for a particular table

1. SELECT  
2.   
3. DISTINCT  
4.   
5. Constraint_Name AS [Constraint],  
6.   
7. Table_Schema AS [Schema],  
8.   
9. Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
10.   
11. WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Table_Name'  
12.   
13. GO  

Example:

Query 11: RESEED Identity of all tables

1. EXEC sp_MSForEachTable '  
2.   
3. IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1  
4.   
5. DBCC CHECKIDENT (''?'', RESEED, 0)  

Example:

Query 12: List of tables with number of records

1. CREATE TABLE #Tab  
2.   
3. (  
4.   
5. Table_Name [varchar](max),  
6.   
7. Total_Records int  
8.   
9. );  
10.   
11. EXEC sp_MSForEachTable @command1=' Insert Into #Tab(Table_Name, Total_Records) SELECT ''?'', COUNT(*) FROM ?'  
12.   
13. SELECT * FROM #Tab t ORDER BY t.Total_Records DESC;  
14.   
15. DROP TABLE #Tab;  

Example:

Query 13: Get the version name of SQL Server

1. SELECT @@VERSION AS Version_Name  

Example:

Query 14: Get Current Language of SQL Server

1. SELECT @@LANGUAGE AS Current_Language;  

Example:

Query 15: Disable all constraints of a table

1. ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL  

Example:

Query16: Disable all constraints of all tables

1. EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  

Example:

Query 17: Get Current Language Id

1. SELECT @@LANGID AS 'Language ID'  

Example:

Query18: Get precision level used by decimal and numeric as current set in Server:

1. SELECT @@MAX_PRECISION AS 'MAX_PRECISION'  

Example:

Query 19: Return Server Name of SQL Server

1. SELECT @@SERVERNAME AS 'Server_Name'  

Example:

Query 20: Get name of register key under which SQL Server is running

1. SELECT @@SERVICENAME AS 'Service_Name'  

Example:

Query 21: Get Session Id of current user process

1. SELECT @@SPID AS 'Session_Id'  

Example:

Query22: Get Current Value of TEXTSIZE option

1. SELECT @@TEXTSIZE AS 'Text_Size'  

Example:

Query 23: Retrieve Free Space of Hard Disk

1. EXEC master..xp_fixeddrives  

Example:

Query24: Disable a Particular Trigger

Syntax:

1. ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name  

Example:

1. ALTER TABLE Employee DISABLE TRIGGER TR_Insert_Salary  

Query 25: Enable a Particular Trigger

Syntax:

1. ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name  

Example:

1. ALTER TABLE Employee ENABLE TRIGGER TR_Insert_Salary  

Query 26: Disable All Trigger of a table

We can disable and enable all triggers of a table using previous query, but replacing the "ALL" instead of trigger name.
Syntax:

1. ALTER TABLE Table_Name DISABLE TRIGGER ALL  

Example:

1. ALTER TABLE Demo DISABLE TRIGGER ALL  

Query 27: Enable All Trigger of a table

1. ALTER TABLE Table_Name ENABLE TRIGGER ALL  

Example:

1. ALTER TABLE Demo ENABLE TRIGGER ALL  

Query 28: Disable All Trigger for database

Using sp_msforeachtable system stored procedure we enable and disable all triggers for a database.

Syntax:

1. Use Database_Name  
2.   
3. Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"  

Example:

Query29: Enable All Trigger for database

1. Use Demo  
2.   
3. Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"  

Example:

Query30: List of Stored procedure modified in last N days

1. SELECT name,modify_date  
2.   
3. FROM sys.objects  
4.   
5. WHERE type='P'  
6.   
7. AND DATEDIFF(D,modify_date,GETDATE())< N  

Example:

Query31: List of Stored procedure created in last N days

1. SELECT name,sys.objects.create_date  
2.   
3. FROM sys.objects  
4.   
5. WHERE type='P'  
6.   
7. AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N  

Example:

Query32: Recompile a stored procedure

1. EXEC sp_recompile'Procedure_Name';  
2.   
3. GO  

Example:

Query 33: Recompile all stored procedure on a table

1. EXEC sp_recompile N'Table_Name';  
2.   
3. GO  

Example:

Query 34: Get all columns of a specific data type:

Query:

1. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name  
2.   
3. FROM sys.columns AS c  
4.   
5. JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
6.   
7. WHERE t.name = 'Data_Type'  

Example:

Query 35: Get all Nullable columns of a table

1. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name  
2.   
3. FROM sys.columns AS c  
4.   
5. JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
6.   
7. WHERE c.is_nullable=0 AND OBJECT_NAME(c.OBJECT_ID)='Table_Name'  

Example:

Query 36: Get All table that don’t have primary key

1. SELECT name AS Table_Name  
2.   
3. FROM sys.tables  
4.   
5. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0  
6.   
7. ORDER BY Table_Name;  

Example:

Query 37: Get All table that don’t have foreign key

1. SELECT name AS Table_Name  
2.   
3. FROM sys.tables  
4.   
5. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0  
6.   
7. ORDER BY Table_Name;  

Example:

Query 38: Get All table that don’t have identity column

1. SELECT name AS Table_Name  
2.   
3. FROM sys.tables  
4.   
5. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0  
6.   
7. ORDER BY Table_Name;  

Example:

Query 39: Get First Date of Current Month

1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) First_Date_Current_Month;  

Example:

Query 40: Get last date of previous month

1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),GETDATE()),105) Last_Date_Previous_Month;  

Example:

Query 41: Get last date of current month

1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) Last_Date_Current_Month;  

Example:

Query 42: Get first date of next month

1. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())+1),105) First_Date_Next_Month;  

Example:

Query 43: Swap the values of two columns

1. UPDATE Table_Name SET Column1=Column2, Column2=Column1  

Example:

Query 44: Remove all stored procedure from database

1. Declare @Drop_SP Nvarchar(MAX)  
2.   
3. Declare My_Cursor Cursor For Select [name] From sys.objects where type = 'p'  
4.   
5. Open My_Cursor  
6.   
7. Fetch Next From My_Cursor Into @Drop_SP  
8.   
9. While @@FETCH_STATUS= 0  
10.   
11. Begin  
12.   
13. Exec('DROP PROCEDURE ' + @Drop_SP)  
14.   
15. Fetch Next From My_Cursor Into @Drop_SP  
16.   
17. End  
18.   
19. Close My_Cursor  
20.   
21. Deallocate My_Cursor  

Example:

Query 45: Remove all views from database

1. Declare @Drop_View Nvarchar(MAX)  
2.   
3. Declare My_Cursor Cursor For Select [name] From sys.objects where type = 'v'  
4.   
5. Open My_Cursor  
6.   
7. Fetch Next From My_Cursor Into @Drop_View  
8.   
9. While @@FETCH_STATUS = 0  
10.   
11. Begin  
12.   
13. Exec('DROP VIEW ' + @Drop_View)  
14.   
15. Fetch Next From My_Cursor Into @Drop_View  
16.   
17. End  
18.   
19. Close My_Cursor  
20.   
21. Deallocate My_Cursor  

Example:

Query 46: Drop all tables

1. EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?'  

Example:

Query 47: Get information of tables’ columns

1. SELECT * FROM INFORMATION_SCHEMA.COLUMNS  
2.   
3. WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=’Table_Name’  

Example:

Query 48: Get all columns contain any constraints

1. SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE  

Example:

Query 49: Get all tables that contain a view

1. SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE  

Example:

Query 50: Get all columns of table that using in views

1. SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE  

Example: