SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database | Journey to SQL Authority with Pinal Dave

UPDATE : SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database This is very long query. Optionally, we can limit the query to return results for one or more than one table. SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN… Continue Reading...

Source: SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Query to Find ByteSize of All the Tables in Database | Journey to SQL Authority with Pinal Dave

SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'    ELSE ISNULL(sob.name, 'unknown') END AS Table_name,    SUM(sys.length) AS Byte_Length FROM sysobjects sob, syscolumns sys WHERE sob.xtype='u' AND sys.id=sob.id GROUP BY sob.name WITH CUBE Reference : Pinal Dave (http://blog.SQLAuthority.com) Continue Reading...

Source: SQL SERVER – Query to Find ByteSize of All the Tables in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database | Journey to SQL Authority with Pinal Dave

I always mark fields to be deprecated with "dep_" as prefix. In this way, after few days, when I am sure that I do not need the field any more I run the query to auto generate the deprecation script. The script also checks for any constraint in the system and auto generate the script… Continue Reading...

Source: SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created | Journey to SQL Authority with Pinal Dave

Following cursor query runs through database and find all the table with certain prefixed ('b_','delete_'). It also checks if the Table is more than certain days old or created before certain days, it will delete it. We can have any other opertation on that table like delete, print or reindex. SET NOCOUNT ON DECLARE @lcl_name… Continue Reading...

Source: SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created | Journey to SQL Authority with Pinal Dave

SQL SERVER – Cursor to Kill All Process in Database | Journey to SQL Authority with Pinal Dave

When you run the script please make sure that you run it in different database then the one you want all the processes to be killed. CREATE TABLE #TmpWho (spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150), hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150)) INSERT INTO #TmpWho EXEC sp_who DECLARE @spid INT DECLARE @tString VARCHAR(15) DECLARE… Continue Reading...

Source: SQL SERVER – Cursor to Kill All Process in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure | Journey to SQL Authority with Pinal Dave

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results. ----Option 1 SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%' ----Option 2 SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE… Continue Reading...

Source: SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure | Journey to SQL Authority with Pinal Dave

SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved. | Journey to SQL Authority with Pinal Dave

To fix the error which occurs after the Windows server name been changed, when trying to update or delete the jobs previously created in a SQL Server 2000 instance, or attaching msdb database. Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job… Continue Reading...

Source: SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved. | Journey to SQL Authority with Pinal Dave