Thursday, March 20, 2014

Sql Server Queries With Example

Leave a Comment
In this tutorial I am listing some SQL Queries which are almost used by every developer during  SQL server database handling as a back-end . In previous article I listed some useful SQL Queries.

Some Important SQL Queries along with Example:

1. Sql Query to Get the Length of a String:

Query:

 SELECT LEN('This is Testing Length string') AS STRINGLENGTH

Output:




2. SQL Query to Convert String to Lower-Case from Uppercase:

Query:


SELECT LOWER('WELCOME') AS LOWERECASE

Output:

sql query to convert string to lowercase

3. SQL Query to Convert String to UpperCase:

Query:

SELECT UPPER('welcome') AS UPPPERCASE

Query:

Sql query to convert string  to upper case

4. Sql Query to list of all tables(table name) in a database:

Query:

SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'

Output:

sql query to get all table name in a database


5. Sql Query to get the name of all Stored Procedure(sp) in a database:

Query:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE ='PROCEDURE'

Output:

Sql query to get all stored procedure in a datbase

6. SQL Query to Get the count(number of  tables) in a Database:

Query:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

Output:

Sql Query to count number of tables in a database

7. SQL Query to Count number of Stored Procedure in a Database:

Query:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE ='PROCEDURE'

Output:

Sql Query to Count number of Stored Procedure in a Databse

8. SQL Query to get all tables name having Primary Key Constraint in a database:
Query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY'

Output:

Sql Query to list all tables with primary key constraint


9. Sql Query to select all tables having Foreign Key Constraint:

Query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'

Output:

Sql query  to show all tables with foreign key constraint


0 comments:

Post a Comment