Skip to main content

SQL Server Tutorial - Database queries

1. Rename Database:

In this query we will learn about How to rename database in SQL Server.
1
EXEC sp_renamedb 'oldName', 'newName'
OR
1
ALTER DATABASE oldName MODIFY NAME = newName

2. Rename Table:

In this query we will learn about How to rename Table in SQL Server.
1
EXEC sp_rename 'OldTableName', 'NewTableName'

3. Rename Table Column:

In this query we will learn about How to rename Table Column in SQL Server.
1
EXEC sp_rename 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

4. Check SQL Server version:

In this query we will learn about How to check version of SQL Server.
1
SELECT @@version

5. Get list of hard drives with free space:

In this query we will learn about How to get list of system hard drives with available free space using SQL Server.
1
EXEC master..xp_fixeddrives

6. Get Id of latest inserted record:

In this query we will learn about How to Id of latest inserted record in SQL Server.
1
2
3
4
INSERT INTO TableName (NAME,Email,Age) VALUES ('Test User','test@gmail.com',30)
     
-- Get Id of latest inserted record
SELECT SCOPE_IDENTITY()

7. Delete duplicate records:

In this query we will learn about How to delete duplicate records in SQL Server.
1
2
3
4
5
6
7
DELETE
FROM TableName
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TableName
GROUP BY DuplicateColumn)

8. Display Text of Stored Procedure, Trigger, View :

In this query we will learn about How to Display Text of Stored Procedure, Trigger, View in SQL Server.
1
2
exec sp_helptext @objname = 'getInfoFromTable' 
-- Here getInfoFromTable is my storedprocedure name

9. Get List of Primary Key and Foreign Key for a particular table :

In this query we will learn about How to get list of Primary Key and Foreign Key for a particular table in SQL Server.
1
2
3
4
5
SELECT  DISTINCT 
Constraint_Name AS [ConstraintName], 
Table_Schema AS [Schema], 
Table_Name AS [Table Name] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='TableName' 

10. Get List of Primary Key and Foreign Key of entire database:

In this query we will learn about How to get list of Primary Key and Foreign Key for a entire database in SQL Server.
1
2
3
4
SELECT  DISTINCT 
Constraint_Name AS [ConstraintName], 
Table_Schema AS [Schema], 
Table_Name AS [Table Name] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
Labels:

Comments

Post a Comment

Popular posts from this blog

Scenario : Cloud Computing

7 comments
Read more

Create Web API in Asp.Net Core MVC with Example

Introduction : Here we will learn how to create web api in  asp.net  core mvc with example or  asp.net  core mvc rest web api tutorial with example or  asp.net  core mvc restful api with example or implement web api using asp.net  core with examples. By using  asp.net  core mvc web api templates we can easily implement restful web api services based on our requirements. To create web api first we need to create new project for that Open visual studio  à  Go to File menu  à select New  à  Project like as shown below  Now from web templates select  Asp.Net Core Web Application  ( .NET Core ) and give name ( CoreWebAPI ) to the project and click  OK  button like as shown below. Once we click  OK  button new template will open in that select  Web API  from Asp.Net Core templates like as shown below Our asp.net core web api project s...
Post a Comment
Read more

SQL Server Tutorial - Date functions

1. Get month names with month numbers: In this query we will learn about  How to get all month names with month number in SQL Server . I have used this query to bind my dropdownlist with month name and month number. 1 2 3 4 5 6 7 8 9 10 11 12 ;WITH months(MonthNumber) AS (      SELECT 0      UNION ALL      SELECT MonthNumber+1      FROM months      WHERE MonthNumber < 12 ) SELECT DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS [MonthName],Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS MonthNumber FROM months ORDER BY Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) ; 2. Get name of current month: In this query we will learn about  How to get name of current month in SQL Server . 1 select DATENAME(MONTH, GETDATE()) AS CurrentMonth 3. Get name of day: In this query we wil...
Post a Comment
Read more