Tuesday, June 10, 2014

automatically create insert statements for table data

CREATE procedure  [dbo].[INS]                             
(                                                         
   @Query  Varchar(MAX)                                                         
)                             
 
AS                            
 
   Set nocount ON                 
 
DEclare @WithStrINdex as INT                           
DEclare @WhereStrINdex as INT                           
DEclare @INDExtouse as INT                            
 
Declare @SchemaAndTAble VArchar(270)                           
Declare @Schema_name  varchar(30)                           
Declare @Table_name  varchar(240)                           
declare @Condition  Varchar(MAX)                             
 
SET @WithStrINdex=0                           
 
SELECT @WithStrINdex=CHARINDEX('With',@Query )                           
, @WhereStrINdex=CHARINDEX('WHERE', @Query)                           
 
IF(@WithStrINdex!=0)                           
Select @INDExtouse=@WithStrINdex                           
ELSE                           
Select @INDExtouse=@WhereStrINdex                           
 
Select @SchemaAndTAble=Left (@Query,@INDExtouse-1)                                                     
select @SchemaAndTAble=Ltrim (Rtrim( @SchemaAndTAble))                           
 
Select @Schema_name= Left (@SchemaAndTAble, CharIndex('.',@SchemaAndTAble )-1)                           
,      @Table_name = SUBSTRING(  @SchemaAndTAble , CharIndex('.',@SchemaAndTAble )+1,LEN(@SchemaAndTAble) )                           
 
,      @CONDITION=SUBSTRING(@Query,@WhereStrINdex+6,LEN(@Query))--27+6                           
 
 
Declare   @COLUMNS  table (Row_number SmallINT , Column_Name VArchar(Max) )                             
Declare @CONDITIONS as varchar(MAX)                             
Declare @Total_Rows as SmallINT                             
Declare @Counter as SmallINT             
 
declare @ComaCol as varchar(max)           
select @ComaCol=''                  
 
Set @Counter=1                             
set @CONDITIONS=''                             
 
INsert INTO @COLUMNS                             
Select  Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FRom INformation_schema.columns Where Table_schema=@Schema_name                             
And table_name=@Table_name        
and Column_Name not in ('SyncDestination','PendingSyncDestination' ,'SkuID','SaleCreditedto')                  
 
select @Total_Rows= Count(1) FRom  @COLUMNS                             
 
             Select @Table_name= '['+@Table_name+']'                     
 
             Select @Schema_name='['+@Schema_name+']'                     
 
While (@Counter<=@Total_Rows )                             
begin                              
--PRINT @Counter                             
 
    select @ComaCol= @ComaCol+'['+Column_Name+'],'           
    FROM @COLUMNS                             
Where [Row_number]=@Counter                         
 
select @CONDITIONS=@CONDITIONS+ ' +Case When ['+Column_Name+'] is null then ''Null'' Else ''''''''+                             
 
 Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  )                             
 
  +'''''''' end+'+''','''                             
 
FROM @COLUMNS                             
Where [Row_number]=@Counter                             
 
SET @Counter=@Counter+1                              
 
End                             
 
select @CONDITIONS=Right(@CONDITIONS,LEN(@CONDITIONS)-2)                             
 
select @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)             
select @ComaCol= substring (@ComaCol,0,  len(@ComaCol) )                           
 
select @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS                             
 
select @CONDITIONS=@CONDITIONS+'+'+ ''')'''                             
 
Select @CONDITIONS= 'Select  '+@CONDITIONS +'FRom  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition                             
print(@CONDITIONS)                             
Exec(@CONDITIONS) 
 
 
 
Exec [dbo].[INS]  'Person.PersonPhone where 1=1'

Thursday, October 24, 2013

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 FK relationship 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

Friday, September 6, 2013

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