Top 30 Important SQL Queries For Developers

In this SQL article, I will introduce you to some important SQL queries and technical points. This article is a guide for you to learn the concepts related to SQL. It starts with the basic SQL queries and later, continues to the advanced ones.

All of us IT professionals are aware of the fact that the SQL database is a very good database solution. So, if you are planning to start a career as SQL developer or DBA, now is the right time to move in.

Let’s get introduced to SQL.

What is SQL?

SQL stands for Structured Query Language as used to interact with the database. Using SQL, we can create a database, create a table,  insert, retrieve, update and delete data in the table. 

What is a Database?

Database is the collection of schemas, tables, queries etc. to help us store and manage the data.

Let’s get started with some basic to advanced SQL queries.

  1. How can we get SQL Server version?
    1. SELECT @@VERSION As [SQL Version]  
    Top 30 Important SQL Query For Developers
  1. How do we get the server property using SQL command?
    1. SELECT SERVERPROPERTY('Edition')  
    Top 30 Important SQL Query For Developers
  1. How we can get stored procedure text using SQL command?

    Top 30 Important SQL Query For Developers
  1. How can we get stored procedure text using SQL Object definition?
    1. SELECT OBJECT_DEFINITION(OBJECT_ID('[SQLDB].[dbo].[insert_EmpDetails]'))  
    Top 30 Important SQL Query For Developers
  1. How can we get created stored procedure Object Id from Database?
    1. SELECT object_id As ObjectID FROM    sys.sql_modules  
    2. WHERE object_id = OBJECT_ID('[SQLDB].[dbo].[insert_EmpDetails]')   
    Top 30 Important SQL Query For Developers
  1. How can we get Table Created Date and Last modified date?
    1. SELECT [TableName] = name,create_date,modify_date FROM sys.tables  
    2. WHERE name = 'EmployeeM'  
    Top 30 Important SQL Query For Developers
  1. When has a user accessed or scanned a table? 
    1. SELECT distinct  [TableName] = OBJECT_NAME(object_id),  
    2. last_user_update, last_user_seek, last_user_scan  
    3. FROM sys.dm_db_index_usage_stats   
    4. WHERE database_id = DB_ID('SQLDB'AND OBJECT_NAME(object_id) = 'EmployeeM'  
    Top 30 Important SQL Query For Developers 
  1. How many connection attempts since SQL started?
    1. SELECT [ConnectionAttempts] = @@CONNECTIONS  
    Top 30 Important SQL Query For Developers

    (Note:Number of connections depends on when SQL started.)
  1. How can we retrieve Database Recovery model information?
    1. SELECT [DatabaseName] = name,[RecoveryModel] = recovery_model_desc  
    2. FROM  sys.databases  
    Top 30 Important SQL Query For Developers
  1. Get Database Id, login time, session id and status:
    1. SELECT   session_id,login_time,database_id,status FROM sys.dm_exec_sessions  
    2. WHERE    DB_NAME(database_id) = 'SQLDB'  
    Top 30 Important SQL Query For Developers
  1. Get database file description and physical name:
    1. SELECT name, type_desc, physical_name FROM sys.database_files  
    Top 30 Important SQL Query For Developers
  1. Get fail mail details of SQL mail server:
    1. SELECT recipients,sent_status,sent_date FROM msdb.dbo.sysmail_faileditems  
    Top 30 Important SQL Query For Developers
  1. Get successfully sent mail details from SQL mail server:
    1. SELECT recipients,sent_status,sent_date FROM msdb.dbo.sysmail_sentitems  
    Top 30 Important SQL Query For Developers
  1. Get System mail event log details:
    1. SELECT log_id, event_type, log_date,  description FROM msdb.dbo.sysmail_event_log  
    2. ORDER BY    log_date DESC  
    Top 30 Important SQL Query For Developers
  1. How can we Get Current User Session Id?
    1. SELECT @@SPID AS CuurentUserSessionId  
    Top 30 Important SQL Query For Developers
  1. SQL Trigger Enable and Disable command

    Disable - alter table EmployeM DISABLE TRIGGER trg_Employee

    Enable - alter table EmployeeM ENABLE TRIGGER trg_Employee

    Top 30 Important SQL Query For Developers
  1. Get table name list
    1. SELECT name as TableName FROM sys.tables  
    Top 30 Important SQL Query For Developers
  1. Get Table column information as like Column name, data type, Nullable type
    1. SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH   
    2. FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='EmployeeM'  
    Top 30 Important SQL Query For Developers
  1. Get comma-separated employee names:
    1. Using: COALESCE function  
    2. DECLARE @NameStr VARCHAR(MAX)   
    3. SELECT @NameStr = COALESCE(@NameStr+',' ,'') + EmpName   
    4. FROM (SELECT DISTINCT EmpName FROM EmployeeM) EmpName  
    5. select @NameStr  
    Top 30 Important SQL Query For Developers
  1. Transport TEXT using SQL:
    1. Declare @str nvarchar(100)='Rakesh';  
    2. Declare @length INT;  
    3. Declare @i INT=1;  
    4. SET @length=LEN(@str)  
    5. while @i<=@length  
    6. BEGIN  
    7. print(substring(@str,@i,1));  
    8. set @i=@i+1;  
    9. END  
    Top 30 Important SQL Query For Developers
  1. Get all User define table list
    1. select Name As UserDefineTable from Sys.objects where Type='u'  
    Top 30 Important SQL Query For Developers
  1. Gel Primary key list from User-defined table
    1. select Name As PrimaryKey from Sys.Objects where Type='PK'  
    Top 30 Important SQL Query For Developers
  1. Find Internal Table from database:
    1. select Name As InternalTables from Sys.Objects where Type='it'  
    Top 30 Important SQL Query For Developers
  1. Write the query for finding available system field data type names:
    1. SELECT name AS [NameFROM sys.types   
    Top 30 Important SQL Query For Developers
  1. Get the list of database schema:
    1. SELECT s.name AS [NameFROM sys.schemas AS s ORDER BY [NameASC  
    Top 30 Important SQL Query For Developers
  1. Get the list of different types of database roles:
    1. select name as [Namefrom sys.database_principals where type = 'R' order by name  
    Top 30 Important SQL Query For Developers
  1. Find the current system's Windows user:
    1. SELECT SYSTEM_USER as WindowsUser  
    Top 30 Important SQL Query For Developers
  1. Get the list of created stored procedures with definition:

    Top 30 Important SQL Query For Developers
  1. Generate Hash password:
    1. DECLARE @HashPassword nvarchar(250) = 'password';  
    2. SELECT HASHBYTES('SHA2_256', @HashPassword);  
    Top 30 Important SQL Query For Developers

I hope you like this article. Stay tune for the next one, and have a nice day.