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';

If you got an error, Login failed. You need to check whether mixed Authentication is enabled or not. Run below query.

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');

If the result is 1, it means Windows Authentication mode is enabled (SQL Server logins are disabled).
If the result is 0, Mixed Mode (Windows and SQL authentication) is enabled.

Change to Mixed Mode Authentication:
Open SQL Server Management Studio (SSMS).
Right-click on the SQL Server instance and select Properties.
Go to the Security page.
Select SQL Server and Windows Authentication mode (Mixed Mode).
Restart the SQL Server service.

• 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