Skip to content

Desi banjara

  • “You can have anything you want if you are willing to give up the belief that you can’t have it.” Dr. Robert Anthony Motivational & Inspirational Quotes
  • If there was a word in the dictionary to describe a woman as loving, wonderful, nurturing, caring and affectionate like you, I’d paint the whole world red with it. Since there is no such word, accept my humble wishes on your Happy Birthday! Birthday Wishes For Mom
  • I believe in angles I believe in superheros I believe in miracles I believe in blessings I believe in good luck I believe in destiny I find all these in my Mom Who is my everything Happy Birthday Birthday Wishes For Mom
  • Sample Exam Questions 3: AZ-300: Microsoft Azure Architect Technologies AZ-300: Microsoft Azure Architect Technologies Exam
  • Happiness Quote – Dream as if you’ll live forever – James Dean Happiness Quote
  • Inspirational Travel Quotes – Travel makes one modest.- Gustave Flaubert Inspirational Travel Quotes
  • “Two roads diverged in a wood and I – I took the one less traveled by.” – Robert Frost Quotes
  • Happy birthday to my mom and thank you for everything you’ve done for me and my kids. I don’t know what I’d do without you. Thank you for teaching me to be respectful and caring adults. I couldn’t do it without you. Birthday Wishes For Mom

Microsoft SQL Server – 50+ useful Sql query

No Comments on Microsoft SQL Server – 50+ useful Sql query

Microsoft SQL Server – 50+ useful Sql query

1- Sql query to get all Views relate to table

SELECT DISTINCT so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ‘%Table_Name%’ AND so.xtype=’V’



2- Sql query to Get Session Id of current user process
SELECT @@SPID AS ‘Session_Id’



3- Sql query to get all table that don’t have identity column

SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasIdentity’) = 0
ORDER BY Table_Name;



4- Sql query to Rebuild All Index of Database

EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”
GO
EXEC sp_updatestats
GO



5-Sql query to Get Current Value of TEXTSIZE option

SELECT @@TEXTSIZE AS ‘Text_Size’



6- Sql query to Get all table that don’t have identity column

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where
Table_NAME NOT IN (
SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c
INNER
JOIN sys.identity_columns sic
on
(c.COLUMN_NAME=sic.NAME))
AND
TABLE_TYPE =’BASE TABLE’



7- Sql query to swap the values of two columns

UPDATE Table_Name SET Column1=Column2, Column2=Column1



8- Sql query to disable a Particular Trigger
ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name

Example:

ALTER TABLE Employee DISABLE TRIGGER TR_Update_qualificationDate


9- Sql query to Find Byte Size of All tables in database

SELECT so.name AS Table_Name,
SUM(sc.length) AS [Size_Table(Bytes)]
FROM sysobjects so, syscolumns sc
WHERE so.xtype=’u’ AND sc.id=so.id
GROUP BY so.name



10- Sql query to get last date of current month

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



11- Retrieve Free Space of Hard Disk

EXEC master..xp_fixeddrives

12- Sql query to retrieve all dependencies of Stored Procedure

;WITH stored_procedures AS (
SELECT
so1.name AS table_name,
ROW_NUMBER() OVER(partition by so.name, so1.name ORDER BY so.name, so1.name) AS row
FROM sysdepends sd
INNER JOIN sysobjects so ON so.id=sd.id
INNER JOIN sysobjects so1 ON so1.id=sd.depid
WHERE so.xtype = ‘P’ AND so.name LIKE ‘%SP_Name%’ )
SELECT Table_name FROM stored_procedures
WHERE row = 1



13- Sql query to get last date of previous month

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

14- Sql query to List of Primary Key and Foreign Key for Whole Database

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
GO



15- Sql query to drop all tables

EXEC sys.sp_MSforeachtable @command1 = ‘Drop Table?’

16- Sql query to enable a Particular Trigger

ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name
Example:
ALTER TABLE Employee ENABLE TRIGGER TR_Update_qualificationDate


17- Sql query to get all columns contain any constraints

SELECT TABLE_NAME, COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE



18- Sql query to return Server Name of SQL Server

SELECT @@SERVERNAME AS ‘Server_Name’



19- SQL Server: Sql query to get information of tables columns

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=’Table_Name’



20- Sql query to get Current Language Id

SELECT @@LANGID AS ‘Language ID’

21- Sql query to disable all constraints of all tables

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’



22- Sql query to retrieve List of All Database

Answer: EXEC sp_helpdb

23- Sql query to List of Stored procedure created in last N days

SELECT name, sys.objects.create_date
FROM sys.objects
WHERE type=’P’
AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N



24- Sql query to display Text of Stored Procedure, Trigger, and View

exec sp_helptext @objname = ‘Object_Name’
In this query Object_name can be stored procedure, trigger or view name.
Example:
exec sp_helptext @objname = ‘sp_GetLatestOrders’

25- Sql query to recompile a stored procedure

EXEC sp_recompile’Procedure_Name’;

26- Sql query to get all stored procedure related to database

SELECT DISTINCT so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE so.xtype=’P’



27- Sql query to recompile all stored procedure on a table

EXEC sp_recompile N’Table_Name’;

28- Sql query to get All Stored Procedure Relate to Table

SELECT DISTINCT so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ‘%Table_Name%’ AND so.xtype=’P’


29- Sql query to get all Nullable columns of a table

SELECT OBJECT_NAME(sc.OBJECT_ID) as Table_Name, sc.name as Column_Name
FROM sys.columns AS sc
JOIN sys.types AS st ON sc.user_type_id=st.user_type_id
WHERE sc.is_nullable=0 AND OBJECT_NAME(sc.OBJECT_ID)=’Table_Name’

30 – Write SQL Query to display current date.

SQL has built in function called GetDate () which returns current timestamp.

31 – Write an SQL Query to find names of students start with ‘A’.

SELECT * FROM Employees WHERE Students like ‘A%’



32- Sql query to list names of all the tables in a sql server database
SELECT * FROM INFORMATION_SCHEMA.TABLES

33- How to delete all duplicate rows from table but keeping one record?

Because of some service error we got lots of duplicate records in our database table. We need to delete all the duplicates but we want to keep one record in our table.

Below was the query which we used to do the same.

WITH deleteDuplicates AS (

SELECT[WebURL], ID,

row_number() OVER(PARTITION BY [WebURL] ORDER BY Id) AS [rownum]

FROM table_name

)

DELETE deleteDuplicates WHERE [rownum] > 1

 



34-Sql query to RESEED Identity of all tables

EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED, 0)

35- Sql query to List of Primary Key and Foreign Key for a particular table

SELECT DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME=’Table_Name’
GO



36- Sql query to get all columns of table that using in views

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

37- Sql query to get name of register key under which SQL Server is running

SELECT @@SERVICENAME AS ‘Service_Name’

38- Sql query to disable all constraints of a table

ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL

39- Sql query to get Current Language of SQL Server

SELECT @@LANGUAGE AS Current_Language;

40- Sql query to get the version name of SQL Server

SELECT @@VERSION AS Version_Name

41- Sql query to List of tables with number of records

CREATE TABLE #Tab
( Table_Name [varchar](max), Total_Records int );
EXEC sp_MSForEachTable @command1=’ Insert Into #Tab(Table_Name, Total_Records) SELECT ”?”, COUNT(*) FROM ?’
SELECT * FROM #Tab t ORDER BY t.Total_Records DESC;
DROP TABLE #Tab;

42- Sql query to List of Stored procedure modified in last N days

SELECT name, modify_date
FROM sys.objects
WHERE type=’P’
AND DATEDIFF(D,modify_date,GETDATE())< N

43- Sql query to Enable All Trigger for database

Use Database_Name
Exec sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

44- Sql query to Disable All Trigger for database

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

Use Database_Name

Exec sp_msforeachtable “ALTER TABLE ? DISABLE
TRIGGER all”

45- Sql query to Enable All Trigger of a table

ALTER TABLE Table_Name ENABLE TRIGGER ALL

Example:

ALTER TABLE MembershipFee ENABLE TRIGGER ALL

46- Sql query to Disable All Trigger of a table

SQL Server: Sql query to 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.

ALTER TABLE Table_Name DISABLE TRIGGER ALL

Example:

ALTER TABLE MembershipFee DISABLE TRIGGER ALL

47- Sql query to get all tables that contain a view

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

48- Sql query to get all table that don’’t have foreign key

SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasForeignKey’) = 0
ORDER BY Table_Name;

49- Sql query to get all table that don’t have primary key

SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasPrimaryKey’) = 0
ORDER BY Table_Name;

50- Sql query to select unique records by SQL

SELECT DISTINCT column 1, column 2, …
FROM table_name;

SQL, SQL Server Tags:Database, IT/Software development, SQL, SQL Server, useful queries

Post navigation

Previous Post: Romantic birthday wishes/messages/sms for boyfriend
Next Post: Entrepreneurship Inspirational Quotes

Related Posts

  • SQL Server Interview questions Interview questions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *



Archives

  • June 2021
  • March 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • July 2020
  • June 2020
  • April 2020
  • December 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • August 2015
  • May 2015
  • April 2014
  • November 2013

Categories

  • Acer
  • Amazon AWS Certification Exam
  • Amazon Kindle
  • Android phones
  • ASP.Net MVC
  • ASP.NET Web API
  • AWS DevOps Engineer Professional Exam
  • AZ-300: Microsoft Azure Architect Technologies Exam
  • Best Wishes Messages
  • birthday messages for boyfriend
  • Birthday messages For Girlfriend
  • Birthday Wishes
  • Birthday Wishes For Mom
  • Business
  • C# development
  • Cameras
  • Canon
  • Cloud
  • Cloud services
  • digital-cameras
  • Diwali
  • Entrepreneurship
  • eReaders
  • Family Quotes
  • Family Quotes
  • Friendship Quotes
  • Gadgets
  • Games
  • Get Well Soon Messages
  • GIT
  • Good Morning Wishes
  • Google
  • Gratitude Quote
  • Guru Nanak Jayanti
  • Halloween
  • Happiness Quote
  • Happy Diwali Wishes
  • Happy Independence Day Wishes
  • Happy New Year Wishes
  • HTC
  • HTC One
  • HTML
  • I Miss You Messages
  • Inspirational Quotes
  • Inspirational Travel Quotes
  • Interview questions
  • IT/Software development
  • Leadership Quote
  • Life lessons
  • Love Quotes
  • Love shayari
  • Messages
  • Microsoft AI-900 Certification Exam
  • Microsoft AZ-104 Certification Exam
  • Microsoft AZ-204 Certification Exam
  • Microsoft AZ-900 Certification Exam
  • Microsoft Azure
  • Microsoft Azure certifications
  • Microsoft Exam AZ-220
  • Microsoft Excel
  • Microsoft Office
  • Microsoft word
  • Mobile phones
  • Motivational & Inspirational Quotes
  • Nature Quotes
  • Nexus
  • Nikon
  • Pixels
  • PL-200: Microsoft Power Platform Functional Consultant Certification
  • PL-900: Microsoft Power Platform Fundamentals
  • postman
  • Quotes
  • Robin Sharma
  • Samsung Galaxy S5
  • Self improvement quotes
  • Self-Confidence Quote
  • SonarQube
  • Sony PlayStation 4
  • SQL
  • SQL Server
  • Success Quotes
  • Travel Quotes
  • Uncategorised
  • Uplifting Quotes
  • WCF (Windows Communication Foundation)
  • Web development
  • Wishes
  • Wishes for Newborn Baby

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org



Recent Posts

  • PL-200: Microsoft Power Platform Functional Consultant Certification – Exam Practice Questions
  • SonarQube – Static code analysis
  • Microsoft PL-900 Certification Exam Practice Questions – 2
  • Microsoft AZ-204 Certification Exam Practice Questions – 1
  • AWS DevOps Engineer Professional Exam Practice Questions – 14

Recent Comments

  • Yosianus on Error while sending json model in POST request to web API service using postman
  • You are rare like a diamond, beautiful like a goddess and pure like an angel. I can’t tell you how blessed I am to have a mother like you. Happy Birthday to you, dear Mom. Birthday Wishes For Mom
  • Interview question: In how many ways you can pass parameters to a method? C# development
  • 100 + Success Quotes – Take up one idea. Make that one idea your life Motivational & Inspirational Quotes
  • “Have great hopes and dare to go all out for them. Have great dreams and dare to live them. Have tremendous expectations and believe in them.”– Norman Vincent Peale Motivational & Inspirational Quotes
  • What is DOM (Document Object Model) in HTML? HTML
  • Famous Buddha Quotes on Life Life lessons
  • IAAS vs PAAS vs SAAS – different types of cloud services Cloud services
  • How many people are lucky enough to treat their headache and fever by the healing strokes of their mother? Happy Birthday Mom Birthday Wishes For Mom

Copyright © 2022 Desi banjara.

Powered by PressBook News WordPress theme