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.
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…Read More
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
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