Get all foreign key relationships referencing your table and drop them

Hi Folks,

Its back to back post today.The script I am sharing today is very useful and I actually

found it way back last year,so thought to share it.

 

–To get all foreign key relationships referencing your table, you could use this SQL (if you’re on SQL Server 2005 and up):
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(TableName)

–and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:
SELECT
    ‘ALTER TABLE ‘ + OBJECT_NAME(parent_object_id) +
    ‘ DROP CONSTRAINT ‘ + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(‘Student’)

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

Advertisements

Find unused jobs in SQL Server

Hi folks,

Today I want to share a useful script which will help us to clutter list of jobs that either have not run in X months or where the next schedule run date is older than today.

Just run this against any server. Investigate if the job can be deleted.

SELECT    @@SERVERNAME SvrName, J.Name, J.[Enabled], JA.LastRun, JA.NextRun, JV.[description], JC.Name JobCategory
FROM    msdb.dbo.sysjobs J
        JOIN msdb.dbo.sysjobs_view JV
            ON J.Job_ID = JV.Job_ID
        JOIN
            (
            SELECT    Job_ID, MAX(Last_Executed_Step_Date) LastRun, MAX(Next_Scheduled_Run_Date) NextRun
            FROM    msdb.dbo.sysjobactivity
            GROUP    BY Job_Id
            ) JA
            ON J.Job_ID = JA.Job_ID
        JOIN msdb.dbo.syscategories JC
            ON J.Category_ID = JC.category_id
WHERE    DATEDIFF(m, ISNULL(LastRun, ‘1900-01-01’), GETDATE()) > 12
        OR NextRun < GETDATE()

 

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

Generating million’s of random dates/times as Test data

Hi folks,

Often we play a lot with dates in SQL Server and need test dates for testing purpose.

The following code generates million random dates/times from the first instant of the year 2000 to the last instant of 2009 (ten full years in total) in about 5 seconds.

 

--===== Do this testing in a nice safe place that everyone has
    USE TempDB
;
--===== Create and populate a test table with a million dates
     -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
     -- Column "SomeDate" has a range of  >=01/01/2000 and 
     -- <01/01/2010 non-unique date/times
 SELECT TOP 1000000
        RowNum   = IDENTITY(INT,1,1),
        SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
   INTO dbo.JBMTest
   FROM sys.All_Columns t1,
        sys.All_Columns t2
;
Why a million dates? For two reasons: 1) I'm a "batch programmer" and million row tables are actually considered 
to be a fairly small table compared to what most batch programmers normally work with and 
2) all of the code runs extremely fast so we need at least that many rows to start seeing the differences.