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

ASP.NET Core - MVC Design Pattern

The MVC (Model-View-Controller) design pattern is a design pattern that's actually been around for a few decades, and it's been used across many different technologies, everything from Smalltalk to C++ to Java and now in C# and .NET as a design pattern to use when you're building a user interface. The MVC design pattern is a popular design pattern for the user interface layer of a software application. In larger applications, you typically combine a model-view-controller UI layer with other design patterns in the application, like data access patterns and messaging patterns. These will all go together to build the full application stack. The MVC separates the user interface (UI) of an application into the following three parts − The Model  − A set of classes that describes the data you are working with as well as the business logic. The View  − Defines how the application’s UI will be displayed. It is a pure HTML which decides how the UI is going to loo...
Post a Comment
Read more
In a cloud computing system, there's a significant workload shift. Local computers no longer have to do all the heavy lifting when it comes to running applications. The network of computers that make up the cloud handles them instead. Hardware and software demands on the user's side decrease. The only thing the user's computer needs to be able to run is the cloud computing system's   interface software , which can be as simple as a Web browser, and the cloud's network takes care of the rest.
1 comment
Read more

ASP.NET MVC 5 - Accessing Model's Data from a Controller

Build the application  before going on to the next step. If you don't build the application, you'll get an error adding a controller. 4 In Solution Explorer, right-click the  Controllers  folder and then click  Add , then  Controller . In the  Add Scaffold  dialog box, click  MVC 5 Controller with views, using Entity Framework , and then click  Add . 1 Select  Movie (MvcMovie.Models)  for the Model class. Select  MovieDBContext (MvcMovie.Models)  for the Data context class. For the Controller name enter  MoviesController . The image below shows the completed dialog. 2 Click  Add . (If you get an error, you probably didn't build the application before starting adding the controller.) Visual Studio creates the following files and folders: 2 A MoviesController.cs  file in the  Controllers  folder. A  Views\Movies  folder. Create.cshtml, Delete.cshtml, Details.cs...
Post a Comment
Read more