June Discount 33% | Comprehensive Database Performance Health Check | Testimonials

SQL SERVER – Cursor to Kill All Process in Database

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
, ,
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…
Read More
All Articles looping-arrows-512x400

SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created

Pinal Dave
SQL Tips and Tricks
, , , ,

Following cursor query runs through the 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 operation on that table like to delete, print or index.

Read More

SQL SERVER – Query to Find ByteSize of All the Tables in Database

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
,
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 (https://blog.sqlauthority.com)
Read More

SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
, ,
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…
Read More
Menu