How To Delete Rows From Tow Tables When They Have Foreign Keys To Each Other



TODO:

You have table A, and Table B.  Table A has a foreign key to table B, and table B has a foreign key to table A.  You need to delete data from table A, but cannot because of the foreign key on Table B.

 

SOLUTION:

ALTER TABLE employee NOCHECK CONSTRAINT ALL
delete employee where id=100
ALTER TABLE employee WITH CHECK CHECK CONSTRAINT ALL


NOTES:

This will allow you to delete data when there arecircular foreign keys.

How To Print Messages In a SQL Server Stored Procedure



TODO:

Have you ever wanted to print output messages in a stored procedure?

 

SOLUTION:

PRINT 'some string'

PRINT 'some string' + @SomeVariable

 

NOTES:

Note, if you are concatenating a numeric variable you will need to cast it to Varchar

How To Add A Column To An Existing Table In SQL Server Using T-SQL



TODO:

Have you ever wanted to add a column to an existing SQL Server Table using T-SQL?

 

SOLUTION:

ALTER TABLE Customer ADD FavoriteColor VARCHAR(20)

 

NOTES:

There are no notes on this topic

How To Use BulkCopy To Insert A DataTable In C#



TODO:

Have you ever wanted to insert the contents of a DataTable efficiently using SqlClient.BulkCopy?

 

SOLUTION:

public static void FillMonteCarloSimulationResults(DataTable InDataTable)
{
     using (SqlBulkCopy bulkCopy = new SqlBulkCopy("myconnectionstring")
     {
          //***NOW Do column mappings.  This maps columns from DataTable (left) to DB Table (right)
          bulkCopy.DestinationTableName = "Results";
          bulkCopy.ColumnMappings.Add("EmployeeId", "EmployeeId");
          bulkCopy.ColumnMappings.Add("Name", "Name");
          bulkCopy.WriteToServer(InDataTable);            
     }
}

 

NOTES:

This example will take the DataTable, map the columns, then preform BulkCopy

How To Attach A SQL Server Database MDF File Without A Log File



TODO:

Recently I had a corrupt log file, and my database was continuously 'Suspect'.  Since this was a test database, I did not care about my corrupt .ldf file.  Therefore, I simply wanted to attach my .mdf file and let it create a new .ldf file.  The SQL below will do just that.

 

SOLUTION:

CREATE DATABASE myDatabase ON
( FILENAME = N'G:\DATA\myDatabase.mdf')
FOR ATTACH
GO

 

NOTES:

This method works when there is only one log file and it is missing.

IF there is more than 1 log file, use 'FOR ATTACH_REBUILD_LOG' rather than 'FOR ATTACH'

 

How to Find And Rebuild Fragmented Indexes In SQL Server Using T-SQL



TODO:

Have you ever wanted to get badly fragmented indexes, and rebuild them using T-SQL?

 

SOLUTION:

CREATE procedure [dbo].[sp_GetIndexFragmentation]
AS

-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(128);
DECLARE @execstr   VARCHAR(255);
DECLARE @objectid  INT;
DECLARE @indexid   INT;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
	+'.'+CAST(TABLE_NAME AS VARCHAR(100))
           AS Table_Name
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName CHAR(255),
   ObjectId INT,
   IndexName CHAR(255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity decimal,
   BestCount INT,
   ActualCount INT,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN;
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';

--UNCOMMENT LINE BELOW TO ACTUALLY DEFRAG   
--EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;

 

NOTES:

There are no notes on this topic

How To Shrink Database File Using DBCC Shrinkfile



TODO:

Have you ever wanted to shrink a database or log using DBCC Shrinkfile?

 

SOLUTION:

DBCC SHRINKFILE(DataOrLogFile, 1)

 

NOTES:

There are no notes on this topic

How To Filter A Tablix With An OR Condition In SQL Server Report Builder



TODO:

Have you ever wanted to filter a tablix using an OR clause.  The interface is a little confusing, but the steps below will accomplish this.

 

SOLUTION:

1.  Open Tablix Properties

2.  Add Filter

3.  Set Filter Expression: =(Fields!MyTagName.Value = "value1" OR Fields!MyTagName.Value = "value2" OR Fields!MyTagName.Value = "value3")

4.  Set Filter Expression Type: Boolean

5.  Set Filter Operator to '='

6.  Set Filter Value = 'True'

 

That should do it.

 

NOTES:

There are no notes on this topic.

How To Fix A Database Stuck In [Recovery Pending] Status In SQL Server Management Studio



TODO:

SQL Server Management Studio reports a database in "Recovery Pending" status. 

 

SOLUTION:

use master

ALTER DATABASE YourDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE YourDatabase SET ONLINE WITH ROLLBACK IMMEDIATE


NOTES:

There are no notes on this topic.

How To INIT CAP Data In T-SQL



TODO:

Have you ever wanted to convert the data in a column to INIT CAP?  For example you have data 'TEST DATA' and you want to change it to 'Test Data'?

 

SOLUTION:

 

CREATE FUNCTION dbo.fCapFirst(@pcIn NVARCHAR(4000))
RETURNS NVARCHAR(4000) AS
BEGIN
DECLARE @i		INT
,  @cWork		NVARCHAR(4000)

SET @cWork = Upper(Left(@pcIn, 1)) + Lower(SubString(@pcIn, 2, 4000))

SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
,  @cWork COLLATE Latin1_General_BIN)

WHILE 0 < @i
   BEGIN
      SET @cWork = Left(@cWork, @i) 
+        Upper(SubString(@cWork, 1 + @i, 1)) + SubString(@cWork, 2 + @i, 4000)
      SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
,        @cWork COLLATE Latin1_General_BIN)
   END

RETURN @cWork
END
GO

 

NOTES:

This solution was posted by user Pat Phelan on this site. 

To run issue this command:  update MyTable set Mycolumn= dbo.fCapFirst(Mycolumn)