MSBI Tutor

-- dbo : new schema name -- pm : old schema name -- Changing schema name from pm to dbo alter schema dbo transfer pm . table_name

SELECT sys.server_role_members.role_principal_id, role.name AS RoleName,       sys.server_role_members.member_principal_id, member.name AS MemberName  FROM sys.server_role_members  JOIN sys.server_principals AS role      ON sys.server_role_members.role_principal_id = role.principal_id  JOIN sys.server_principals AS member      ON sys.server_role_members.member_principal_id = member.principal_id;        

SELECT DB_NAME ( database_id ) AS DatabaseName , CAST ( [Name] AS varchar ( 20 )) AS NameofFile , CAST ( physical_name AS varchar ( 100 )) AS PhysicalFile , type_desc AS FileType , (( size * 8 )/ 1024 ) AS FileSize , MaxFileSize = CASE WHEN max_size = - 1 OR max_size = 268435456 THEN 'UNLIMITED' WHEN max_size = 0 THEN 'NO_GROWTH' WHEN max_size <> - 1 OR max_size <> 0 THEN CAST ((( max_size * 8 ) / 1024 ) AS varchar ( 15 )) ELSE 'Unknown' END , SpaceRemainingMB = CASE WHEN max_size = - 1 OR max_size = 268435456 THEN 'UNLIMITED' WHEN max_size <> - 1 OR max_size = 268435456 THEN CAST (((( max_size - size ) * 8 ) / 1024 ) AS varchar ( 10 )) ELSE 'Unknown' END , Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE' WHEN growth > 0 THEN (( growth * 8 )/ 1024 ) ELSE 'Unknown' END , GrowthType = CASE

TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server 'Server_Name'.  (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: File "DataBase_Name" cannot be restored over the existing "D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DataBase_Name.mdf". Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476 ------------------------------ BUTTONS:

SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers. SQL Server Configuration Manager is a Microsoft Management Console snap-in that is available from the Start menu, or can be added to any other Microsoft Management Console display. Microsoft Management Console ( mmc.exe ) uses the  SQLServerManager<version>.msc  file (such as  SQLServerManager13.msc  for SQL Server 2016 (13.x)) to open Configuration Manager. Here are the paths to the last four versions when Windows in installed on the C drive. SQL Server 2017 C:\Windows\SysWOW64\SQLServerManager14.msc SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc SQL Server 2014 (12.x) C:\Windows\SysWOW64\SQLServerManager12.msc SQL Server 2012 (11.x)

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool. Note DBCC FREEPROCCACHE does not clear the execution statistics for natively compiled stored procedures. The procedure cache does not contain information about natively compiled stored procedures Syntax for SQL Server: DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]   Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse: DBCC FREEPROCCACHE [ ( COMPUTE | ALL ) ]      [ WITH NO_INFOMSGS ]    [;]   Arguments ( {  plan_handle  |  sql_handle  |  pool_name  } ) plan_handle  uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache.  plan_handle  is  varbinary(64)  and can be obtained from the following dynamic management objects: ·          sys.dm_ex

CREATE FUNCTION [ dbo ].[ fn_GetWorstPerformingSPs ] (    @n SMALLINT = 10 ,    @dbname SYSNAME = '%' ,    @avg_time_threshhold INT = 0         ) RETURNS TABLE AS RETURN (    SELECT TOP ( @n )        DB_NAME ( database_id ) AS DBName ,       OBJECT_SCHEMA_NAME ( object_id , database_id ) AS [ Schema_Name ],       OBJECT_NAME ( object_id , database_id ) AS [ Object_Name ],       total_elapsed_time / execution_count AS Avg_Elapsed_Time ,       ( total_physical_reads + total_logical_reads ) / execution_count AS Avg_Reads ,       execution_count AS Execution_Count ,       t . text AS Query_Text ,       H . query_plan AS Query_Plan    FROM        sys . dm_exec_procedure_stats       CROSS APPLY sys . dm_exec_sql_text ( sql_handle ) T        CROSS APPLY sys . dm_exec_query_plan ( plan_handle ) H    WHERE        LOWER ( DB_NAME ( database_id )) LIKE LOWER ( @dbname )        AND total_elapsed_time / execution_count &