July 12, 2012 at 10:18 am
We have alot of small databases and most of our work is adhoc stuff. So people create alot of tables and then never touch them again. This is the process I came up with to figure out what needs to be deleted purged or archived.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* -- =============================================
-- Author: T. Mark Fink
-- Create date: 07/11/2012
-- Description: Builds Info schema on all databases and tables.
then so it can be read by vwDatabase_Table_info.
This is a listing of records and last modified dates.
This is designed to assist in the purging of data or the
elimination of Data
**** Note this will only report tables with either indexes or primary keys set *******
On every instance of SQL Server installed I keep a database to store code that can be used accross the instance. I know you can use master but I choose to do it this way.
-- 1st This is the schema you have to create in the database of your choice
CREATE SCHEMA [Info] AUTHORIZATION [dbo]
-- These are the tables you need to create
2nd
CREATE TABLE [Info].[AllDatabases](
[Database_name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_size] [nvarchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_owner] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_id] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_created] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[status] [varchar](Max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[compatibility_level] [tinyint] NULL,
[Database_SQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Run_id] [int] IDENTITY(1,1) NOT NULL) ON [PRIMARY]
3rd
CREATE TABLE [Info].[AllTableCounts](
[Run_id] [int] IDENTITY(1,1) NOT NULL,
[Count_Database_Name] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Count_Schema_Table_Name] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Count_Schema] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Count_Table_Name] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Count_Table_Count] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
4th
CREATE TABLE [Info].[AllTables](
[SchemaTable] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_Name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_Schema] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_Table_Name] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_Table_Type] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
5th
CREATE TABLE [Info].[AllTables_Dates](
[AllTable_Dates_id] [int] IDENTITY(1,1) NOT NULL,
[Database_Name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_Schema] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Database_Table_Name] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Last_user_Seek] [smalldatetime] NULL,
[Last_user_lookup] [smalldatetime] NULL,
[Last_user_update] [smalldatetime] NULL,
[Modify_Date] [smalldatetime] NULL,
[Create_Date] [smalldatetime] NULL
) ON [PRIMARY]
-- 6th
============================================= */
Create PROCEDURE [dbo].[Sproc_00_Database_Info]
AS
BEGIN
SET NOCOUNT ON;
/*Get the database you are going to use */
Declare @MainDatabase nvarchar(25)
Set @MainDatabase = (Select db_Name())
/*Clear out tables */
Truncate Table info.AllDatabases
Truncate Table info.AllTables
Truncate Table info.AllTableCounts
Truncate Table Info.AllTables_Dates
/* Build a list of all the databases */
Insert into info.AllDatabases ([Database_name]
,[Database_size]
,[Database_owner]
,[Database_id]
,[Database_created]
,[status]
,[compatibility_level]) Exec SP_Helpdb
/* This clears out the system databases Master Model MSDB Distribution Tempdb
*/
----Select * From Info.AllDatabases
Delete From info.AllDatabases where Database_Name in ('master','model','msdb','tempdb')
Declare @q char(1)
Set @q = char(39)
/*This builds a command that creates the list of all the tables in each database */
Update info.AllDatabases
Set Status = null,
Database_SQL = 'Insert into info.AllTables (SchemaTable,Database_Name,Database_Schema,Database_Table_Name,Database_Table_Type)
Select Table_Schema' + '+' + @q + '.' + @q + ' + ' + 'Table_Name,
Table_Catalog,
Table_Schema,
Table_Name,
Table_Type
From ' + Database_Name + '.INFORMATION_SCHEMA.TABLES
Where Table_Type =' + @q + 'BASE TABLE' + @q
/* This Executes the command built above and inserts the records into info.AllTables */
Declare @max-2 int
Set @max-2 = (Select max(Run_id) from info.AllDatabases)
Declare @Thisjob int
Set @Thisjob = 1
Declare @ExecCmd nvarchar(Max)
While @Thisjob <= @max-2
Begin
If (Select Database_name from info.AllDatabases where Run_id = @Thisjob) is not null
Begin
Set @ExecCmd = (Select Rtrim(Ltrim(Database_SQL)) from info.AllDatabases where Run_id = @Thisjob)
-- Select @ExecCmd
EXECUTE (@ExecCmd)
End
Set @Thisjob = @Thisjob + 1
End
/* This builds a command to insert the counts of all the tables into a table*/
Update info.AllDatabases
Set Database_SQL = 'Insert into info.AllTableCounts(Count_Database_Name,Count_Schema_Table_Name,Count_Schema,
Count_Table_Name,Count_Table_Count) SELECT '+ @q +Database_Name + @q + ', sc.name' + '+' + @q + '.' + @q + ' + ' + 'ta.name as FullName,
sc.name Schema_Name, ta.name Table_Name
,SUM(pa.rows) RowCnt
FROM '+ Database_Name + '.sys.tables ta
INNER JOIN '+ Database_Name + '.sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN '+ Database_Name + '.sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC'
/* This Executes the command built above and inserts the records into info.AllTableCounts */
Set @max-2 = (Select max(Run_id) from info.AllDatabases)
Set @Thisjob = 1
While @Thisjob <= @max-2
Begin
If (Select Database_name from info.AllDatabases where Run_id = @Thisjob) is not null
Begin
Set @ExecCmd = (Select Rtrim(Ltrim(Database_SQL)) from info.AllDatabases where Run_id = @Thisjob)
-- Select @ExecCmd
EXECUTE (@ExecCmd)
End
Set @Thisjob = @Thisjob + 1
End
/* This builds a command to insert the last dates of all the tables into a table*/
Update info.AllDatabases
Set Database_SQL = 'use ' + Database_Name +' ; INSERT INTO [' + @MainDatabase + '].[Info].[AllTables_Dates]
([Database_Name]
,[Database_Schema]
,[Database_Table_Name]
,[Last_user_Seek]
,[Last_user_lookup]
,[Last_user_update]
,[Modify_Date]
,[Create_Date])
Select DB_Name() as Database_Name,
sc.name as Schema_Name,
t.name Table_Name,
Last_user_Seek,
Last_user_lookup,
Last_user_update,
t.Modify_Date,
t.Create_Date
From sys.dm_db_index_usage_stats i
inner join sys.tables t on (t.object_id = i.object_id)
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = t.OBJECT_ID
INNER JOIN sys.schemas sc
ON t.schema_id = sc.schema_id
where database_id = db_id()'
/* This Executes the command built above and inserts the records into info.AllTable_dates */
Set @max-2 = (Select max(Run_id) from info.AllDatabases)
Set @Thisjob = 1
While @Thisjob <= @max-2
Begin
If (Select Database_name from info.AllDatabases where Run_id = @Thisjob) is not null
Begin
Set @ExecCmd = (Select Rtrim(Ltrim(Database_SQL)) from info.AllDatabases where Run_id = @Thisjob)
-- Select @ExecCmd
EXECUTE (@ExecCmd)
End
Set @Thisjob = @Thisjob + 1
End
/* This is for testing*/
--Select * From info.Alldatabases
--Select * From info.AllTables
--Select * From Info.AllTableCounts
--Select * From Info.AllTables_Dates
/* 7th This is the view you need to create to read the files.
CREATE VIEW [dbo].[vwDatabase_Table_Info]
AS
SELECT Distinct Info.AllDatabases.Database_name, Info.AllTables.Database_Schema, Info.AllTables.Database_Table_Name, Info.AllTableCounts.Count_Table_Count,
Info.AllTables_Dates.Last_user_Seek, Info.AllTables_Dates.Last_user_lookup, Info.AllTables_Dates.Last_user_update,
Info.AllTables_Dates.Modify_Date, Info.AllTables_Dates.Create_Date
FROM Info.AllDatabases INNER JOIN
Info.AllTables ON Info.AllDatabases.Database_name = Info.AllTables.Database_Name INNER JOIN
Info.AllTableCounts ON Info.AllTables.Database_Name = Info.AllTableCounts.Count_Database_Name AND
Info.AllTables.Database_Schema = Info.AllTableCounts.Count_Schema AND
Info.AllTables.Database_Table_Name = Info.AllTableCounts.Count_Table_Name INNER JOIN
Info.AllTables_Dates ON Info.AllTables.Database_Name = Info.AllTables_Dates.Database_Name AND
Info.AllTables.Database_Schema = Info.AllTables_Dates.Database_Schema AND
Info.AllTables.Database_Table_Name = Info.AllTables_Dates.Database_Table_Name
8th
DECLARE @rc int
EXECUTE @rc = [dbo].[Sproc_00_Database_Info]
last
SELECT [Database_name]
,[Database_Schema]
,[Database_Table_Name]
,[Count_Table_Count]
,[Last_user_Seek]
,[Last_user_lookup]
,[Last_user_update]
,[Modify_Date]
,[Create_Date]
FROM [dbo].[vwDatabase_Table_Info]
*/
/* To clean up and start over */
-- DROP VIEW [dbo].[vwDatabase_Table_Info]
-- DROP TABLE [Info].[AllDatabases]
-- DROP TABLE [Info].[AllTableCounts]
-- DROP TABLE [Info].[AllTables]
-- DROP TABLE [Info].[AllTables_Dates]
-- DROP PROCEDURE [dbo].[Sproc_00_Database_Info]
-- DROP SCHEMA [Info]
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
July 12, 2012 at 10:36 am
One suggestion, change ON [PRIMARY] to ON [DEFAULT].
One reason, for me, is that when I create new databases I add at least one new file group, Data, and I make that the default file group. This separates the user created objects from the system objects. I may actually create other file groups as well: NCIndexes, IndexedViews. Even if these all exist on the same disk during development (and potentially in Test, QA, and even Production), it makes it easier to move the files to separate disks if (or when) available.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply