Top 30 Important SQL Queries For Developers

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?
   

   SELECT @@VERSION As [SQL Version]

   
   

2. How do we get the server property using SQL command?
   

   SELECT SERVERPROPERTY('Edition')

   
   

3. How we can get stored procedure text using SQL command?
   
   
   

4. How can we get stored procedure text using SQL Object definition?
   

   SELECT OBJECT_DEFINITION(OBJECT_ID('[SQLDB].[dbo].[insert_EmpDetails]'))

   
   

5. How can we get created stored procedure Object Id from Database?
   

   SELECT object_id As ObjectID FROM    sys.sql_modules
   WHERE object_id = OBJECT_ID('[SQLDB].[dbo].[insert_EmpDetails]')

   
   

6. How can we get Table Created Date and Last modified date?
   

   SELECT [TableName] = name,create_date,modify_date FROM sys.tables
   WHERE name = 'EmployeeM'

   
   

7. When has a user accessed or scanned a table? 
   

   SELECT distinct  [TableName] = OBJECT_NAME(object_id),
   last_user_update, last_user_seek, last_user_scan
   FROM sys.dm_db_index_usage_stats
   WHERE database_id = DB_ID('SQLDB') AND OBJECT_NAME(object_id) = 'EmployeeM'

    

8. How many connection attempts since SQL started?
   

   SELECT [ConnectionAttempts] = @@CONNECTIONS

   
   
   (Note:Number of connections depends on when SQL started.)

9. How can we retrieve Database Recovery model information?
   

   SELECT [DatabaseName] = name,[RecoveryModel] = recovery_model_desc
   FROM  sys.databases

   
   

10. Get Database Id, login time, session id and status:
    

    SELECT   session_id,login_time,database_id,status FROM sys.dm_exec_sessions
    WHERE    DB_NAME(database_id) = 'SQLDB'

    
    

11. Get database file description and physical name:
    

    SELECT name, type_desc, physical_name FROM sys.database_files

    
    

12. Get fail mail details of SQL mail server:
    

    SELECT recipients,sent_status,sent_date FROM msdb.dbo.sysmail_faileditems

    
    

13. Get successfully sent mail details from SQL mail server:
    

    SELECT recipients,sent_status,sent_date FROM msdb.dbo.sysmail_sentitems

    
    

14. Get System mail event log details:
    

    SELECT log_id, event_type, log_date,  description FROM msdb.dbo.sysmail_event_log
    ORDER BY    log_date DESC

    
    

15. How can we Get Current User Session Id?
    

    SELECT @@SPID AS CuurentUserSessionId

    
    

16. SQL Trigger Enable and Disable command
    
    Disable - alter table EmployeM DISABLE TRIGGER trg_Employee
    
    Enable - alter table EmployeeM ENABLE TRIGGER trg_Employee
    
    
    

17. Get table name list
    

    SELECT name as TableName FROM sys.tables

    
    

19. Get Table column information as like Column name, data type, Nullable type
    

    SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='EmployeeM'

    
    

20. Get comma-separated employee names:
    

    Using: COALESCE function
    DECLARE @NameStr VARCHAR(MAX)
    SELECT @NameStr = COALESCE(@NameStr+',' ,'') + EmpName
    FROM (SELECT DISTINCT EmpName FROM EmployeeM) EmpName
    select @NameStr

    
    

21. Transport TEXT using SQL:
    

    Declare @str nvarchar(100)='Rakesh';
    Declare @length INT;
    Declare @i INT=1;
    SET @length=LEN(@str)
    while @i<=@length
    BEGIN
    print(substring(@str,@i,1));
    set @i=@i+1;
    END

    
    

22. Get all User define table list
    

    select Name As UserDefineTable from Sys.objects where Type='u'

    
    

23. Gel Primary key list from User-defined table
    

    select Name As PrimaryKey from Sys.Objects where Type='PK'

    
    

24. Find Internal Table from database:
    

    select Name As InternalTables from Sys.Objects where Type='it'

    
    

25. Write the query for finding available system field data type names:
    

    SELECT name AS [Name] FROM sys.types

    
    

26. Get the list of database schema:
    

    SELECT s.name AS [Name] FROM sys.schemas AS s ORDER BY [Name] ASC

    
    

27. Get the list of different types of database roles:
    

    select name as [Name] from sys.database_principals where type = 'R' order by name

    
    

28. Find the current system's Windows user:
    

    SELECT SYSTEM_USER as WindowsUser

    
    

29. Get the list of created stored procedures with definition:
    
    
    

30. Generate Hash password:
    

    DECLARE @HashPassword nvarchar(250) = 'password';
    SELECT HASHBYTES('SHA2_256', @HashPassword);

    
    

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