Important SQL Queries

1. Create table:

create table table_name
(

id int,
name varchar(20),
salary int

)

2. Create a table with a primary key

create table table_name
(

id int primary key,
name varchar(20),
salary int

)  

Table Designer:

3. Insert multiple rows in a single column

insert into table_name (id,name,salary) values (1,'ritika', 45000), (2,'rishi', 43000), (3,'rahul',54000);

select * from table_name

Table Designer:

 4. Update all records

update table_name set salary=40000

select * from table_name

Table Designer:

 5. Create a view

create view

view_name as select id,name,salary from table_name where (salary < 50000)

select * from view_name

Table View:

6. Get all column names from a table :

select column_name from INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME= 'employee1' ;

Table View:

7. Get all user-created tables:

select name from sys.objects where type='U' ;

Result View:

8. Get all views names:

select * from sys.views

9. Get all stored procedures name from the database:

select * from sys.procedures

10. Counting rows of all tables at once:

select [table_name] = so.name, [rowcount] = MAX (si.rows)
from sysobjects so, sysindexes si where so.xtype = 'U' and si.id = OBJECT_ID(so.name)group by so.name order by 2 desc

Result View:

11.  Retrieve a list of all database names

exec sp_databases

Result View:

12. Check default language of SQL server

select @@LANGUAGE as DefaultLanguage

Result View:

13. Check server name

select @@SERVERNAME as ServerName

14. Add columns to the existing table:

alter table {table_name} add {columnName} {data_type}

example:

alter table table_name add location varchar(20)

         

Result View:

15. Remove column from a table:

alter table {table_name}

drop column {column_name}

16. Rebuild all index of the database

exec sp_MSforeachtable @command1 = "print '?' DBCC DBREINDEX ('?' , ' ' , 80)"

Result View:

17. Find bytesize of all tables in database

select sob.name as table_name, sum(sys.length ) as [size_table(bytes)] from sysobjects sob, syscolumns sys where sob.xtype = 'U' and sys.id = sob.id group by sob.name

Result View:

18. List of the primary key and foreign key for the whole database

select distinct constraint_name as [constraint], table_schema as [schema], table_name as [tablename] from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Result View:

19. Delete duplicate rows:

delete from table_name where id not in 
( select max(id) from table_name group by duplicateColumn1 , duplicateColumn2)

20. Reseed identity of all tables

exec sp_MSforeachtable '

if objectproperty(object_id("?") , "tableHasIdentity") = 1

DBCC checkIDENT ("?" , RESEED , 0) '

21. Disable all constraints of a table:

alter table table_name nocheck constraint all

22. Get current language ID

select @@LANGID as 'language id'

Result View:

23. Get precision level used by decimal and numeric as current set in the server

select @@MAX_PRECISION as 'MAX_Preicision'

Result View:

24. Get the name of register key under which SQL server is running

select @@SERVICENAME as 'Service_Name'

Result View:

25. Get session Id of the current user process

select @@SPID as 'Session_Id'

Result View:

26. Get the current value of text_Size

set TEXTSIZE 0; 

select @@TEXTSIZE as 'Text_Size'

set TEXTSIZE 1324;

select @@TEXTSIZE as 'Text_Size'

set TEXTSIZE 11456;

select @@TEXTSIZE as 'Text_Size'

Result View:

27. Retrieve free space of the hard disk

exec master..xp_fixeddrives

Result View:

28. Disable a particular trigger

alter table table_name disable trigger trigger_name

29. Enable all Triggers:

alter table table_name enable trigger trigger_name

30. Disable all triggers for the database:

use database_name

exec sp_MSforeachtable "alter table ? disable trigger a11"

31. Enable all triggers for the database:

use database_name

exec sp_MSforeachtable "alter table ? enable trigger a11"

32. Get all columns of a specific datatype:

select object_name(c.object_ID) as table_name , c.name as column_name

from sys.columns as c

join sys.types as t on c.user_type_id = t.user_type_id

where t.name = 'DataType'  

33. Get all nullable columns of a table

select object_name (c.object_id) as table_name , c.name as column_name

from sys.columns as c

join sys.types as t on c.user_type_id = t.user_type_id

where c.is_nullable = 0 and object_name(c.object_id) = 'table_name'

Result View:

34. Get all tables that don’t have a primary key

select name as table_name from sys.tables where OBJECTPROPERTY(object_id,'tableHasPrimaryKey') = 0 order by table_name;

Result View:

35. Get all tables that do not have a foreign key

select name as table_name from sys.tables where OBJECTPROPERTY(object_id,'tableHasForeignKey') = 0 order by table_name;

Result View:

36. Get all tables that don’t have an identity column

select name as table_name from sys.tables where OBJECTPROPERTY(object_id,'tableHasIdentity') = 0 order by table_name;

Result View:

37. Get the first date of the current month

select convert(varchar(20) , dateAdd(day, - (day(getDate())) +1 , getDate()), 105) first_data_current_month;

Result View:

38. Get the last date of the previous month

select convert(varchar(20) , dateAdd(day, - (day(getDate())) , getDate()), 105) last_date_previous_month;

Result View:

39. Get the first date of next month

select convert(varchar(20) , dateAdd(day, - (day(getDate())) , dateAdd (month , 1, GetDate()) +1 ) , 105 ) first_date_Next_month;

Result View:

40. Swap the values of two columns:

update table_name set column1=column2 , column2 = column1