Thursday, October 24, 2013

Script to rename all tables Starting with "wd_" with script in SQL Server


-- using my tempdb as a sandbox...
USE tempdb
GO
CREATE TABLE wd_table01(x int)
CREATE TABLE wd_table02(x int)
CREATE TABLE wd_table03(x int)
GO
IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables;
SELECT TABLE_NAME AS tbl
INTO #tables
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE 'wd_%'
DECLARE @sql varchar(8000);
SELECT @sql=
(       SELECT 'exec sp_rename '''+tbl+''', '''+REPLACE(tbl,'wd_','')+''';'+CHAR(10)
        FROM #tables
        FOR XML PATH(''));
EXEC(@sql);

Wednesday, October 2, 2013

How do I create foreign Keyrelationship with a table in a different database?

You would need to manage the referential constraint across databases using a Trigger.
Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.
Example:
Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin

   If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END

END

Edited: Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you.

Credits:John Hartsock

Tuesday, August 27, 2013

Secure Orphan UserS AutoFix

When you restore a database on a different SQL Server, all SQL users become a "orphaned user" because the SQL logins have a different SID then on the source server.
In SQL Server 2005 you can fix this orphaned user with the stored procedure "sys.sp_change_users_login", but since version 2008 this SP is deprecated.
This Transact-SQL statement also links orphaned user to existing principals with same name.
In regard of security this script don't add a new principal if none with same name was found; as the SP with option AUTO_FIX do.
Works with SQL Server 2005 and higher versions in all editions.
Requires ALTER ANY USER permissions.
Links:
sys.sp_change_users_login: http://msdn.microsoft.com/en-us/library/ms174378.aspx
ALTER USER: http://msdn.microsoft.com/en-us/library/ms176060.aspx

 
 
 
 
SQL
Skript bearbeiten|Remove
-- Secure Orphaned User AutoFix 
DECLARE @autoFix bit; 
SET @autoFix = 'FALSE';  -- FALSE = Report only those user who could be auto fixed. 
                         -- TRUE  = Report and fix !!! 
 
DECLARE @user sysname, @principal sysname, @sql nvarchar(500), @found int, @fixed int; 
 
DECLARE orphans CURSOR LOCAL FOR 
    SELECT QUOTENAME(SU.[name]) AS UserName 
          ,QUOTENAME(SP.[name]) AS PrincipalName 
    FROM sys.sysusers AS SU 
         LEFT JOIN sys.server_principals AS SP 
             ON SU.[name] = SP.[name] 
                AND SP.[type] = 'S' 
    WHERE SU.issqluser = 1          -- Only SQL logins 
          AND NOT SU.[sid] IS NULL  -- Exclude system user 
          AND SU.[sid] <> 0x0       -- Exclude guest account 
          AND LEN(SU.[sid]) <= 16   -- Exclude Windows accounts & roles 
          AND SUSER_SNAME(SU.[sid]) IS NULL  -- Login for SID is null 
    ORDER BY SU.[name]; 
 
SET @found = 0; 
SET @fixed = 0; 
OPEN orphans; 
FETCH NEXT FROM orphans 
    INTO @user, @principal; 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    IF @principal IS NULL 
        PRINT N'Orphan: ' + @user; 
    ELSE 
    BEGIN 
        PRINT N'Orphan: ' + @user + N' => Autofix possible, principal with same name found!'; 
        IF @autoFix = 'TRUE' 
        BEGIN 
            -- Build the DDL statement dynamically. 
            SET @sql = N'ALTER USER ' + @user + N' WITH LOGIN = ' + @principal + N';'; 
            EXEC sp_executesql @sql; 
            PRINT N'        ' + @user + N' is auto fixed.'; 
            SET @fixed = @fixed + 1; 
        END 
    END 
    SET @found = @found + 1; 
     
    FETCH NEXT FROM orphans 
        INTO @user, @principal; 
END; 
 
CLOSE orphans; 
DEALLOCATE orphans; 
 
PRINT ''; 
PRINT CONVERT(nvarchar(15), @found) + N' orphan(s) found, ' 
    + CONVERT(nvarchar(15), @fixed) + N' orphan(s) fixed.';

Friday, August 23, 2013

Send an Alert thru email on number of User Connections

IF (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name='User Connections')>0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'Mymai@MyMailcorp.com',
--@body = @bodyText,
@body_format = 'TEXT',
@subject = 'Testing Email' ;

Thursday, August 22, 2013

Query to find total number of logical reads/physical reads done, when a given stored procedure is executed


SELECT ss.sum_execution_count
,t.TEXT
,ss.sum_total_elapsed_time
,ss.sum_total_worker_time
,ss.sum_total_logical_reads
,ss.sum_total_logical_writes
FROM (SELECT s.plan_handle
,SUM(s.execution_count) sum_execution_count
,SUM(s.total_elapsed_time) sum_total_elapsed_time
,SUM(s.total_worker_time) sum_total_worker_time
,SUM(s.total_logical_reads) sum_total_logical_reads
,SUM(s.total_logical_writes) sum_total_logical_writes
FROM sys.dm_exec_query_stats s
GROUP BY s.plan_handle
) AS ss
CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t
WHERE t.TEXT LIKE '%PROCEDURE NAME HEREt%'
ORDER BY sum_total_logical_reads DESC



If you want to collect this data for a specific execution, you need to save the data before execution into a table, and then after execution read the DMV again to compute the delta. A presumptions is that there are no other executions of the procedure at the same time.

Rather than using dm.sys_exec_query_stats, you can use dm.sys_exec_procedure_stats, so that you get values on procedure level instead rather than on query level.

Wednesday, August 21, 2013

Changing the font color of a row dynamically using sp_send_dbmail

declare @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>Test Data</H1>' +
N'<H4>TEST_sub_header</H4>' +
N'<H4>TEST_header_1</H4>' +
N'<table border="1">' +
N'<tr><th>place</th><th>Temperature</th>' +
N'</tr>' +
CAST ( ( select case when Temperature>40 then 'red' else 'black' end as "font/@color",
place as "font/td",'',
case when Temperature>40 then 'red' else 'black' end as "font/@color",
Temperature as "font/td"
from test_fontcolor
order by place
for xml path('tr')
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name='test',
@recipients='test@microsoft.com',
@from_address = 'storenote@microsoft.com',
@subject = 'Test Font Color',
@body = @tableHTML,
@body_format = 'HTML' ;

Tuesday, August 20, 2013

Enable or Disable Logins with Stored Procedure

create procedure toggle_login (@login_name nvarchar(125), @set_enable bit)
as
begin

declare @sql_command nvarchar(500)
set @sql_command = 'ALTER LOGIN [' + @login_name + '] ' + case when @set_enable = 1 then 'ENABLE' else 'DISABLE' end
print @sql_command
exec sp_executesql @sql_command
end

 
--usage of above stored procedure

exec toggle_login 'domain\loginname,0