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

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

.NET Core 2.0 Changes – 4 Key Things to Know

1. .NET Standard 2.0 Expanded APIs & the Ability to Reference Full Framework Libraries .NET Standard broadens the set of APIs available to include a lot of the missing features. It now supports 32,000+ APIs. It is now much easier to port your code to a .NET Standard library without major code changes. One of the biggest problems with .NET Core was the lack of third-party libraries. For example, when 1.0 came out, popular logging libraries like log4net were not even available (it is now). However, this was really only a problem if you wanted to deploy your app on Mac or Linux. You could have used .NET Core and targeted full .NET framework and not had these issues. .NET Standard 2.0 has added a  new compatibility shim  that will enable any .NET Core app to reference any full framework library. 2. Expanded OS Support One of the big goals with .NET Core is portability across multiple operating systems. Including desktops, servers, and even mobile. Microsoft ...
Post a Comment
Read more