Important SQL Queries

 Here we will discuss some of the important SQL queries.

• Get Database list

        SELECT name as DB_Name,create_date as CreatedDate
        FROM sys.databases

• Get row count in all the Database tables

           SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' +                                                     QUOTENAME(sOBJ.name) AS [TableName]
           ,SUM(sPTN.Rows) AS [RowCount]
           FROM  sys.objects AS sOBJ  INNER JOIN sys.partitions AS sPTN
           ON sOBJ.object_id = sPTN.object_id
           WHERE sOBJ.type = 'U'    AND sOBJ.is_ms_shipped = 0x0
           AND index_id < 2 -- 0:Heap, 1:Clustered
           GROUP BY sOBJ.schema_id , sOBJ.name
           ORDER BY [TableName]

• Recent Modified stored procedures and tables

        SELECT name, create_date, modify_date ,type
        FROM sys.objects
        WHERE type = 'P' or  type = 'U'
        ORDER BY modify_date DESC

• The last query runs on the database

          SELECT  text,*
          FROM    sys.dm_exec_query_stats AS deqs
          CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
          WHERE   deqs.last_execution_time > '5/16/2022'
          order by creation_time desc

• Check user roles in the database

          SELECT ServerName=@@servername, dbname=db_name(db_id()),
           p.name as UserName, p.type_desc as TypeOfLogin, 
          pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
          FROM sys.database_role_members roles
          JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
          JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

• CTE for Recursive Query

    WITH CTE AS      
    (        
    SELECT 1 Number        
    UNION ALL        
    SELECT Number + 1 FROM CTE WHERE Number<20        
    ) 
    SELECT * FROM CTE

• Create a new user from the query

        CREATE LOGIN [ServiceUser] WITH PASSWORD = 'Admin@27'
        CREATE USER [ServiceUser] FOR LOGIN [ServiceUser] WITH DEFAULT_SCHEMA=[dbo]

Note: Run Create Login query on Master DB, Run User Query on specific DB access.

If you want to create a new login for an Azure AD user then run the below query on Master DB

        

  CREATE LOGIN [ademail] from external provider;

If you want to create a user in the specific DB then use the below query

CREATE USER [User1] WITH PASSWORD = 'Test@12', DEFAULT_SCHEMA = dbo; 

Assign Roles to newly created User

        EXEC sys.sp_addrolemember 'db_datareader', 'serviceuser';
        EXEC sys.sp_addrolemember 'db_datawriter', 'serviceuser'; 
        EXEC sys.sp_addrolemember 'db_owner', 'ServiceUser';

• While loop

      DECLARE @ComponentCode int=0

      WHILE EXISTS (SELECT 1 FROM QualityList WHERE QualityId > @ComponentCode)
      BEGIN          
        SELECT @ComponentCode = MIN(QualityId) FROM QualityList 
	  WHERE QualityId > @ComponentCode          
       
	  SELECT @ComponentCode 
      END

• Drop all tables
  

    SELECT  'DROP TABLE [' + name + '];' FROM    sys.tables

     Run the query copy all the table commands and execute.

• Remove all Stored Procedures with Query

DECLARE @procedureName VARCHAR(500)
DECLARE cur CURSOR
 
    FOR SELECT [name] FROM sys.objects WHERE TYPE = 'p'
    OPEN cur
    FETCH NEXT FROM cur INTO @procedureName
    WHILE @@fetch_status = 0
    BEGIN
        EXEC('DROP PROCEDURE [' + @procedureName + ']')
        FETCH NEXT FROM cur into @procedureName
END
CLOSE cur
DEALLOCATE cur

• Calculate SQL/Azure SQL Database Size

        SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024  AS DbSizeInMB
FROM sys.dm_db_partition_stats