/ notes / sql

Sql Azure

Indexes and statistics maintenance script

https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-maintain-azure-sql-indexes-and-statistics/ba-p/368787
https://github.com/yochananrachamim/AzureSQL/blob/master/AzureSQLMaintenance.txt

Contained datebase users

-- Create a 'contained database' user
CREATE USER Test1 WITH PASSWORD='aaaaHD&GFG/D%$!~3K984Bl*'  

-- Assign roles
EXEC sp_addrolemember 'db_datareader', 'Test1';
EXEC sp_addrolemember 'db_datawriter', 'Test1';

-- List users/roles
SELECT 
	members.[name] as user_name,
	roles.[name] as role_name
FROM sys.database_role_members 
    JOIN sys.database_principals roles ON database_role_members.role_principal_id = roles.principal_id
    JOIN sys.database_principals members ON database_role_members.member_principal_id = members.principal_id
ORDER BY 	
	members.[name], 
	roles.[name]
	
-- Delete the user
DROP USER Test1

Count tables rows

SELECT OBJ.name AS ObjectName ,PS.row_count
FROM sys.dm_db_partition_stats AS PS
     INNER JOIN sys.objects AS OBJ ON PS.object_id = OBJ.object_id
     INNER JOIN sys.indexes AS IDX ON PS.object_id = IDX.object_id AND PS.index_id = IDX.index_id
WHERE IDX.is_primary_key = 1
ORDER BY OBJ.name

Clear plan cache

Like the “old” DBCC FREEPROCCACHE

select count(*) from sys.dm_exec_cached_plans;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
select count(*) from sys.dm_exec_cached_plans;

Error importing Azure Bacpac into local sql server

If case of errors like this:

Warning SQL72012: The object [XXXXX_Data] exists in the target, but it will not be dropped...
Warning SQL72012: The object [XXXXX_Log]  exists in the target, but it will not be dropped... 
Error SQL72014: .Net SqlClient Data Provider: Msg 12824, Level 16, State 1, Line 5 The sp_configure value 
                'contained database authentication' must be set to 1 in order to alter a contained database.  
		You may need to use RECONFIGURE to set the value_in_use.
Error SQL72045: Script execution error.  The executed script:

run this on the target sql server - master database:

sp_configure 'contained database authentication', 1;  
GO  
RECONFIGURE;  
GO