Tag Archives: tables

Comparing two tables for equality with Spark SQL

Spark, sql, , , , , , , , , , , , , ,

The best way of comparing two tables to determine if they are the exact same is to calculate the hash sum of each table and then compare the sum of hash. The benefit of the technique below are that no matter how many fields there are and no matter what data types the fields may be, you can use following query to do the comparison:

SELECT SUM(HASH(*)) FROM t1;
SELECT SUM(HASH(*)) FROM t2;

Of course if the schemas of the two tables are different this will by default produce different hash values.

How to grant a User SELECT permission on multiple tables in SQL Server using T-SQL and Excel Formulas

Database Administrator, dba, Excel, SQL Server, T-SQL, tsql, , , , , , , , , , , ,

Maybe you get emails from time to time saying something like “Hey can you grant so-and-so SELECT permission on” and then they list a few dozen tables.

There’s a couple of issues with this.

Firstly you shouldn’t be getting that as a simple email, it should come in as a formal access request.

Secondly User access should be defined in roles (or group logins if you want to manage access at an Active Directory level) that tie back to departments and seniority. Different roles have different permissions on different objects. This makes the subject of access more manageable and easily auditable. The access request should be “can you add so-and-so to this role” and ideally people should only exist in one role.

Thirdly this would be really annoying and, depending on the length of the table list, take too long to do via the SSMS GUI.

So if you are getting emails like the above try move your organisation along with regards the first two points. But to help you action the email I’ve created the Excel file DbaScripts_GrantSelect which can be downloaded here.

Grant Select Excel Sheet Snippet

The DbaScripts_GrantSelect file allows you to enter the Login (user name), Database name and Schema name in the first three columns. You can then copy and paste the table names into the fourth column called Table. Drag the first three columns down for as many table name entries there are. Then drag the SQL Command formula column down for as many table name entries there are and this will create the commands to grant SELECTs on the tables for the user specified.

If you can’t download the file above you can recreate it.

In an empty Excel sheet write the following into the cells as directed.

A1: Login
B1: Database
C1: Schema
D1: Table
E1: SQL Command

In E2 paste the following formula:

=”GRANT SELECT ON [“&B2&”].[“&C2&”].[“&D2&”] TO [“&A2&”];”

 

How to rename and/or remove tables in SQL Server with T-Sql generated by Excel formulas

Database Administrator, dba, Excel, Form, How To, Project Management, Reporting, SQL Server, tsql, , , , , , , , , , , , , , ,

This post deals with using an Excel file to generate T-Sql code to rename and/or remove tables given a scenario like the following. (To generate T-Sql to remove tables using T-sql see this post.)

Say someone sends you a list via an email or text file of tables they want renamed or removed from a database . You could go into SSMS object explorer and rename or delete each table in the list one by one. Or you could write the T-Sql statements individually but chances are you can speed things up using Excel.

With Excel you can input the schema and table name into a given cell and the T-Sql code will be generated to rename and drop the table using formulas.

To do this you can just download the Excel file template here. Download

Rename And Drop Script Generator

The template is setup assuming you are intending on the dropping the table sometime in the future but first you will be renaming it.

A good approach for removing objects is to rename the objects first. This makes it easier to put the environment back the way it was if there are any problems encountered. After a set period of time if there is no negative impact on the overall environment you can script out the object then drop it. (Obviously do this in a test environment first if possible)

To aid further in a cleanup project the Excel file also acts as a form that can be used to track progress as it contains the columns RenameDate, RestoreDate and DropDate. It also contains the column RestoreOriginalName. This column holds the formula to create the T-Sql code to renamed the tables back if there are any problems encountered.

You can adjust the formula in cell D2 to somethings other than _DELETE_ if you want to change the prefix so the tables will be renamed something else. If you just want to remove the tables you’ll have to run the script from column D before you can drop the tables using the script from column F.

Remember to drag the formula down for as many table entries as you have and it will generate the T-Sql needed.

You can create the Excel file manually yourself without downloading it.

To do so open a new Excel file and in an empty sheet name the first 9 columns as below:

A1: DatabaseName
B1: SchemaName
C1: TableName
D1: RenameForDeletion
E1: RestoreOriginalName
F1: DropTable
G1: RenameDate
H1: RestoreDate
I1: DropDate

For D2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”.”&C2&”‘, ‘_DELETE_”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For E2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”._DELETE_”&C2&”‘, ‘”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For F2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN DROP TABLE [“&B2&”].[_DELETE_”&C2&”] END;”

To test that the scripts generated work you can create the mock database and table using the script below. The Excel file is loaded with these values by default.

CREATE DATABASE [TidBytez];
GO

USE [TidBytez]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer] ([ID] [int] NULL) ON [PRIMARY]
GO

 

How to archive tables that contain a keyword from one database to another using T-Sql

Data, Database Administrator, dba, Development, How To, SQL Server, T-SQL, tsql, , , , , , , , , , , ,

In the previous article I wrote about how to identify and remove unwanted tables. Link

In that tutorial I suggested prefixing the tables you want to remove with _DELETE_.

I also suggested you may want to archive the tables in some manner before you delete them. This could be because there is a chance someone might come looking for the data that was in a deleted table or something might break by removing the table and you might want to put it back asap.

If you have only a few tables you could script the tables out but if you have a lot of tables that becomes a little unmanageable. Also if the tables total in size to 10 Gb the script to recreate the tables will be a lot lot larger.

An alternative method is to create an Archive database, copy the tables across to this target database and then delete the tables in the source database. You can then backup and drop the Archive database saving the .bak file somewhere cheaper.

The script below will allow you to do just that. It prints the T-Sql to do the job, it doesn’t carry out the job, so it’s completely safe to execute and review.

To use the script below create a target database.

Use the target database name for the variable value @TargetDb

Use the source database name for the variable value @SourceDb

The @KeyWord variable is used to gather all the tables that contain the string of choice, in the example below _DELETE_.

The @RemoveKeyWord variable is a flag that will remove the keyword string from the target database table name, e.g. _DELETE_Sales will become Sales.

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
	DROP TABLE #Table
GO

DECLARE @RemoveKeyWord BIT
DECLARE @Id AS INT
DECLARE @KeyWord AS VARCHAR(256)
DECLARE @SourceDb AS SYSNAME
DECLARE @TargetDb AS SYSNAME
DECLARE @TableName AS SYSNAME
DECLARE @SchemaName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @RemoveKeyWord = 1
SET @KeyWord = '_DELETE_'
SET @TargetDb = 'Archive'
SET @SourceDb = 'Source'
SET NOCOUNT ON

CREATE TABLE #Table (
	Id_Table INT IDENTITY(1, 1)
	,SchemaName SYSNAME
	,TableName SYSNAME
	);

SET @Sql = '
INSERT INTO #Table (
	SchemaName
	,TableName
	)
SELECT s.NAME
	,so.NAME
FROM ' + QUOTENAME(@SourceDb) + '.sys.tables AS so
LEFT JOIN ' + QUOTENAME(@SourceDb) + '.sys.schemas AS s ON so.schema_id = s.schema_id
WHERE so.NAME LIKE ' + '''' + '%' + @KeyWord + '%' + '''' + '
ORDER BY s.NAME ASC'

EXEC (@Sql)

SET @SchemaName = ''

WHILE @SchemaName IS NOT NULL
BEGIN
	SET @SchemaName = (
			SELECT MIN(SchemaName)
			FROM #Table
			WHERE SchemaName > @SchemaName
				AND SchemaName <> 'dbo'
			)

	PRINT 'USE ' + QUOTENAME(@TargetDb) + ';
GO
	
IF NOT EXISTS (SELECT * FROM ' + QUOTENAME(@TargetDb) + '.sys.schemas WHERE name = ' + '''' + @SchemaName + '''' + ')
  BEGIN
    EXEC (' + '''' + 'CREATE SCHEMA ' + QUOTENAME(@SchemaName) + ';' + '''' + ');
  END;
  
'
END

SET @Id = 1

WHILE @Id IS NOT NULL
BEGIN
	SELECT @TableName = TableName
		,@SchemaName = SchemaName
	FROM #Table
	WHERE Id_Table = @Id

	IF @RemoveKeyWord = 1
	BEGIN
		PRINT '
SELECT *
INTO ' + QUOTENAME(@TargetDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(REPLACE(@TableName, @KeyWord, '')) + ' 
FROM ' + QUOTENAME(@SourceDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
	END
	ELSE
	BEGIN
		PRINT '
SELECT *
INTO ' + QUOTENAME(@TargetDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' 
FROM ' + QUOTENAME(@SourceDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
	END

	SET @Id = (
			SELECT MIN(Id_Table)
			FROM #Table
			WHERE Id_Table > @Id
			)
END

How to identify and remove unused tables in SQL Server with T-Sql

Data, Database Administrator, dba, Development, Excel, How To, Reporting, SQL Server, T-SQL, tsql, , , , , , , , , , , , , , , , , ,

In a perfect world an organisation should never get itself into the situation where tables need to be identified as functionally obsolete and removed in bulk. Schema changes should be stepped through the cycle of development, test, staging and live with developers cleaning as they go, but we don’t live in a perfect world as you may have noticed.

What do I mean by functionally obsolete? This means the tables are no longer being interacted with by user generated objects like stored procedures, functions or views. There may also be tables that could be classified as business redundant. That is they are being referenced by user generated objects frequently but they no longer have a use to the business, i.e. one job may have been replaced by another without the former being disabled or dropped. Business redundant objects are more difficult to determine and finding them may require input from multiple stakeholders.

A good approach for removing objects is to rename the objects first. This makes it easier to put the environment back the way it was if there are any problems encountered. After a set period of time if there is no impact on the overall environment script out the object then drop it. (Obviously do this in a test environment first if possible)

The script below is mostly a light weight SELECT statement that can be run on any environment. It does not execute any of the code it generates. It uses the sys.dm_db_index_usage_stats dynamic management view to determine when the tables were last interacted with. Interaction being defined as the following actions being applied against the object, update, seek, scan, lookup.

Caveat: Entries in this view reset to NULL after a Server reboot. Also the DMV has been known to be a bit unreliable with earlier versions of SQL Server with cases of the view being reset when a full index rebuild is carried out against a table. So don’t execute any code without first reviewing it.

Tables with NULL values for the fields below should be tables that have not been referenced at all or at least since the last time the server was rebooted.

LastUserUpdate
LastUserSeek
LastUserScan
LastUserLookup

Removing these objects is the low hanging fruit of cleaning up an environment. The script also provides stats on how many times these interactions happened and the size of the object. All these stats together should help you determine if an object is functionally redundant or business redundant and can be removed.

To aid further in the cleanup the script also creates the fields Action, Comments, Renamed, RenamedDate, RenameForDeletion, RestoreOriginalName, DropTable, DroppedDate.

The script output can then be copied and pasted into an Excel spread sheet and used to coordinate and track the cleanup progress.

SET NOCOUNT ON

DECLARE @Database TABLE ([DbName] [sysname])
DECLARE @DbName AS [sysname]
DECLARE @Sql AS [varchar] (max)

IF OBJECT_ID('tempdb..#TableStats', 'U') IS NOT NULL
	DROP TABLE #TableStats

IF OBJECT_ID('tempdb..#IndexStats', 'U') IS NOT NULL
	DROP TABLE #IndexStats

IF OBJECT_ID('tempdb..#TableUsageStats', 'U') IS NOT NULL
	DROP TABLE #TableUsageStats

IF OBJECT_ID('tempdb..#TableSizeStats', 'U') IS NOT NULL
	DROP TABLE #TableSizeStats

CREATE TABLE #TableStats (
	[DbName] [sysname]
	,[SchemaName] [sysname]
	,[ObjectId] [bigint]
	,[TableName] [sysname]
	,[ModifiedDate] [datetime]
	);

CREATE TABLE #IndexStats (
	[DbName] [sysname]
	,[ObjectId] [bigint]
	,[HasIndex] [bit]
	);

CREATE TABLE #TableSizeStats (
	[DbName] [varchar](255) NULL
	,[SchemaName] [varchar](255) NULL
	,[ObjectId] [bigint]
	,[TableName] [varchar](255) NULL
	,[RowCount] [bigint] NULL
	,[AvailableSpacePercentage] [numeric](6, 2) NULL
	,[UnusedSpaceGb] [numeric](10, 3) NULL
	,[UsedSpaceGb] [numeric](10, 3) NULL
	,[TotalSpaceGb] [numeric](10, 3) NULL
	,[UnusedSpaceMb] [numeric](13, 3) NULL
	,[UsedSpaceMb] [numeric](13, 3) NULL
	,[TotalSpaceMb] [numeric](13, 3) NULL
	,[UnusedSpaceKb] [bigint] NULL
	,[UsedSpaceKb] [bigint] NULL
	,[TotalSpaceKb] [bigint] NULL
	)

CREATE TABLE #TableUsageStats (
	[DbName] [sysname]
	,[ObjectId] [bigint]
	,[TableName] [nvarchar](128) NULL
	,[LastUserUpdate] [datetime] NULL
	,[LastUserSeek] [datetime] NULL
	,[LastUserScan] [datetime] NULL
	,[LastUserLookup] [datetime] NULL
	,[UserUpdateCount] [bigint] NOT NULL
	,[UserSeekCount] [bigint] NOT NULL
	,[UserScanCount] [bigint] NOT NULL
	,[UserLookupCount] [bigint] NOT NULL
	);

SET @DbName = '';

INSERT INTO @Database (DbName)
SELECT NAME
FROM sys.databases 
WHERE NAME NOT IN (
		'tempdb'
		,'master'
		,'mode'
		,'model'
		)
AND state_desc = 'ONLINE'
ORDER BY NAME ASC;

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			);
	SET @Sql = '
INSERT INTO #TableStats (
	DbName
	,schemaName
	,ObjectId
	,TableName
	,ModifiedDate
	)
SELECT DbName
	,SchemaName
	,ObjectId
	,TableName
	,ModifiedDate
FROM (
SELECT DISTINCT ' + '''' + @DbName + '''' + ' AS DbName
	,s.NAME AS SchemaName
	,t.object_id AS ObjectId
	,t.NAME AS TableName
	,t.modify_date AS ModifiedDate
FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
INNER JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s ON t.schema_id = s.schema_id
LEFT JOIN ' + QUOTENAME(@DbName) + '.sys.extended_properties AS ep ON ep.major_id = t.[object_id] /*Exclude System Tables*/
WHERE t.NAME IS NOT NULL
	AND s.NAME IS NOT NULL
	AND (ep.[name] IS NULL OR ep.[name] <> ''microsoft_database_tools_support'')
	) AS rd
WHERE rd.SchemaName IS NOT NULL
ORDER BY DbName ASC
	,TableName ASC;
'

	EXEC (@Sql)

	SELECT @Sql = '
INSERT INTO #IndexStats (
	DbName
	,ObjectId
	,HasIndex
	)
SELECT ' + '''' + @DbName + '''' + ' AS DbName
	,OBJECT_ID AS ObjectId
	,IndexCheck AS HasIndex
FROM (
	SELECT DISTINCT OBJECT_ID
		,CASE 
			WHEN (
					[TYPE] > 0
					AND is_disabled = 0
					)
				THEN 1
			ELSE 0
			END AS IndexCheck
	FROM ' + QUOTENAME(@DbName) + '.sys.indexes
	) AS rd
WHERE rd.IndexCheck = 1
'

	EXEC (@Sql)

	SET @Sql = 
		'
INSERT INTO #TableSizeStats (
	[DbName]
	,[SchemaName]
	,[ObjectId]
	,[TableName]
	,[RowCount]
	,[AvailableSpacePercentage]
	,[UnusedSpaceGb]
	,[UsedSpaceGb]
	,[TotalSpaceGb]
	,[UnusedSpaceMb]
	,[UsedSpaceMb]
	,[TotalSpaceMb]
	,[UnusedSpaceKb]
	,[UsedSpaceKb]
	,[TotalSpaceKb]
	)
SELECT DISTINCT rd.[DbName]
	,rd.[SchemaName]
	,rd.[ObjectId]
	,rd.[TableName]
	,rd.[RowCount]
	,CASE 
		WHEN TotalSpaceKb > 0
			THEN ((UnusedSpaceKb / TotalSpaceKb) * 100)
		ELSE 0
		END AS AvailableSpacePercentage
	,CONVERT(NUMERIC(10, 3), (rd.[UnusedSpaceKb] / 1024.) / 1024.) AS UnusedSpaceGb
	,CONVERT(NUMERIC(10, 3), (rd.[UsedSpaceKb] / 1024.) / 1024.) AS UsedSpaceGb
	,CONVERT(NUMERIC(10, 3), (rd.[TotalSpaceKb] / 1024.) / 1024.) AS TotalSpaceGb
	,CONVERT(NUMERIC(13, 3), (rd.[UnusedSpaceKb] / 1024.)) AS UnusedSpaceMb
	,CONVERT(NUMERIC(13, 3), (rd.[UsedSpaceKb] / 1024.)) AS UsedSpaceMb
	,CONVERT(NUMERIC(13, 3), (rd.[TotalSpaceKb] / 1024.)) AS TotalSpaceMb
	,rd.[UnusedSpaceKb]
	,rd.[UsedSpaceKb]
	,rd.[TotalSpaceKb]
FROM (
	SELECT ' 
		+ '''' + @DbName + '''' + ' AS DbName
		,t.Object_id AS ObjectId
		,s.[name] AS [SchemaName]
		,t.[name] AS [TableName]
		,p.[rows] AS [RowCount]
		,SUM(a.[used_pages]) * 8 AS [UsedSpaceKb]
		,(SUM(a.[total_pages]) - SUM(a.[used_pages])) * 8 AS [UnusedSpaceKb]
		,SUM(a.[total_pages]) * 8 AS [TotalSpaceKb]
	FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s ON t.schema_id = s.schema_id
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.indexes AS i ON t.OBJECT_ID = i.object_id
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.partitions AS p ON i.object_id = p.OBJECT_ID
		AND i.[index_id] = p.[index_id]
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.allocation_units a ON p.[partition_id] = a.[container_id]
	WHERE t.[is_ms_shipped] = 0
		AND i.OBJECT_ID > 255
	GROUP BY s.[name]
		,t.[name]
		,t.[object_id]
		,p.[rows]
	) AS rd
ORDER BY DbName ASC
	,SchemaName ASC
	,TableName ASC;
'

	EXEC (@Sql)

	SET @Sql = '
INSERT INTO #TableUsageStats (
	[DbName]
	,[ObjectId]
	,[TableName]
	,[LastUserUpdate]
	,[LastUserSeek]
	,[LastUserScan]
	,[LastUserLookup]
	,[UserUpdateCount]
	,[UserSeekCount]
	,[UserScanCount]
	,[UserLookupCount]
	)
SELECT DbName
	,ObjectId
	,TableName
	,LastUserUpdate
	,LastUserSeek
	,LastUserScan
	,LastUserLookup
	,UserUpdateCount
	,UserSeekCount
	,UserScanCount
	,UserLookupCount
FROM (
	SELECT DISTINCT ROW_NUMBER() OVER (
			PARTITION BY ius.Object_Id ORDER BY last_user_update DESC
			) AS RN
		,' + '''' + @DbName + '''' + ' AS DbName
		,ius.OBJECT_ID AS ObjectId
		,o.NAME AS TableName
		,ius.last_user_update AS LastUserUpdate
		,ius.last_user_seek AS LastUserSeek
		,ius.last_user_scan AS LastUserScan
		,ius.last_user_lookup AS LastUserLookup
		,ius.user_updates AS UserUpdateCount
		,ius.user_seeks AS UserSeekCount
		,ius.user_scans AS UserScanCount
		,ius.user_lookups AS UserLookupCount
	FROM ' + QUOTENAME(@DbName) + 
		'.sys.dm_db_index_usage_stats AS ius
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.objects AS o ON ius.OBJECT_ID = o.OBJECT_ID
		AND o.NAME IS NOT NULL
	) AS rd
WHERE rd.RN = 1
ORDER BY rd.DbName ASC
	,rd.TableName ASC
	,rd.LastUserUpdate DESC
	,rd.LastUserSeek DESC
	,rd.LastUserScan DESC
	,rd.LastUserLookup DESC;
'

	EXEC (@Sql)
END;
GO

SELECT DISTINCT ROW_NUMBER() OVER (
		ORDER BY ts.[DbName] ASC
			,ts.[SchemaName] ASC
			,ts.[TableName] ASC
		) AS Row
	,ts.[DbName]
	,ts.[SchemaName]
	,ts.[TableName]
	,'' AS Action --Rename, keep etc.
	,'' AS Comments
	,'' AS Renamed --boolean flag
	,'' AS RenamedDate 
	,i.[HasIndex] --Tables without an index are heaps
	,tss.[TotalSpaceMb] AS TableSizeInMb
	,ts.[ModifiedDate]
	,tus.[LastUserUpdate]
	,tus.[LastUserSeek]
	,tus.[LastUserScan]
	,tus.[LastUserLookup]
	,tus.[UserUpdateCount]
	,tus.[UserSeekCount]
	,tus.[UserScanCount]
	,tus.[UserLookupCount]
	,tss.[AvailableSpacePercentage]
	,tss.[UnusedSpaceGb]
	,tss.[UsedSpaceGb]
	,tss.[TotalSpaceGb]
	,tss.[UnusedSpaceMb]
	,tss.[UsedSpaceMb]
	,tss.[TotalSpaceMb]
	,tss.[UnusedSpaceKb]
	,tss.[UsedSpaceKb]
	,tss.[TotalSpaceKb]
	,'USE ' + QUOTENAME(ts.[DbName]) + '; EXEC sp_rename ' + '''' + ts.[SchemaName] + '.' + ts.[TableName] + '''' + ', ' + '''' + '_DELETE_' + ts.[TableName] + '''' + ';' AS RenameForDeletion
	,'USE ' + QUOTENAME(ts.[DbName]) + '; EXEC sp_rename ' + '''' + ts.[SchemaName] + '.' + '_DELETE_' + ts.[TableName] + '''' + ', ' + '''' + ts.[TableName] + '''' + ';' AS RestoreOriginalName
	,'USE ' + QUOTENAME(ts.[DbName]) + '; DROP TABLE ' + QUOTENAME(ts.[SchemaName]) + '.' + '[' + '_DELETE_' + ts.[TableName] + ']' + ';' AS 'DropTable'
	,'' AS DroppedDate
FROM #TableStats AS ts
LEFT JOIN #TableSizeStats AS tss ON ts.ObjectId = tss.ObjectId
	AND tss.DbName = ts.DbName
LEFT JOIN #IndexStats AS i ON ts.ObjectId = i.ObjectId
	AND i.DbName = ts.DbName
LEFT JOIN #TableUsageStats AS tus ON ts.ObjectId = tus.ObjectId
	AND tus.DbName = ts.DbName
ORDER BY ts.[DbName] ASC
	,ts.[SchemaName] ASC
	,ts.[TableName] ASC

	-- REF: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

How to determine what stored procedures relate to what tables

SQL Server, , , , , , ,

Foreign keys are usually a good indicator of which tables connect to each other however you may be working in an environment that does not always follow best practices. As well as that you may not know what store procedures relate to what tables.
Sometimes databases need to move from one server to another or are depreciated. Before this can happened it is a very good idea to do a compressive assessment to see what interacts with what.
As once a thing gets moved everything that queries against it needs to point to it its new location.
A good starting point of this assessment would be to use the query below to determine what stored procedures reference the table specified in the WHERE clause.

  • NOTES: This query only pulls the tabled referenced in the database, it does not pull tables that are referenced from other databases.
  • Although the table maybe reference it may not actually be interacted with by the query, the table name may be comment in the code for example. However for the most part it is more likely the table has some action performed against it, be it a SELECT, INSERT, UPDATE etc.

The query works by using the sysobjects and syscomments tables.
Sysobjects: a system table that contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.

Syscomments: a system table that contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.

Or to dumb it down it joins, in our case, the stored procedures name to the query and scans this query for references to the specified table.

/*
CHANGE THE FOLLOWING:
SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
TABLE NAME: TableName
 */


USE DatabaseName;
GO

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%TableName%'

To expand on this logic to return every table referenced in every stored procedure in a database run the code below:

/*
CHANGE THE FOLLOWING:
SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
 */
 
USE DatabaseName;
GO 

--DROP TEMP TABLES
IF OBJECT_ID('tempdb..#TablesAndSPs') IS NOT NULL
	DROP TABLE #TablesAndSPs

IF OBJECT_ID('tempdb..#Tables') IS NOT NULL
	DROP TABLE #Tables
	
--DECLARE VARIABLES
DECLARE @tableID AS INT
DECLARE @tableName AS VARCHAR(255)

--CREATE TEMP TABLE TO HOLD THE NAMES OF THE TABLES AND SPs
CREATE TABLE #TablesAndSPs (
	TABLE_ID INT
	,TABLE_NAME VARCHAR(255)
	,SP VARCHAR(255)
	)

--GET A LIST OF THE TABLES IN DATABASE
SELECT row_number() OVER (
		ORDER BY TABLE_NAME
		) AS TABLE_ID
	,TABLE_NAME
INTO #Tables
FROM INFORMATION_SCHEMA.TABLES

SET @tableID = 1

--LOOP AND POPULATE #TablesAndSPs
WHILE @tableID <= (
		SELECT MAX(TABLE_ID)
		FROM #Tables
		)
BEGIN
	SET @tableName = (
			SELECT TABLE_NAME
			FROM #Tables
			WHERE TABLE_ID = @tableID
			)

	INSERT INTO #TablesAndSPs
	SELECT DISTINCT @tableID
		,@tableName
		,so.NAME
	FROM syscomments sc
	INNER JOIN sysobjects so ON sc.id = so.id
	WHERE sc.TEXT LIKE '%' + @tableName + '%'

	SET @tableID = @tableID + 1
END

SELECT * FROM #TablesAndSPs

How to find columns from all Tables of a Database

Excel, GDPR, Reporting, sql, SQL Server, SSRS, SSRS Reporting, T-SQL, , , , , , , , , , , , ,

If your job is to create reports using SQL chances are you have or will encounter this situation:

You’ve been asked to prepare a report, but the person who has asked for the report simply has a list of fields they want and they have no idea where those fields come from. They may have received previous reports in the past, so they know the fields exist, but they cannot provide any of the SQL queries used to create these reports as an example.

You, the developer, may not be familiar with that particular area of the business or associated data sources. Possibly because you typically prepared financial reports and this request has come from the operations or marketing departments.

So the first step is to locate these columns within the database.

The following query will return the Table Name, Schema Name and Column Name from the database.

In the example below all instances where the column name equals CustomerID, OrderID, OrderDate will be returned. Also Column names that contain the word Status or Promotion will also be return. Simply change or add additional columns names as needed. 

USE [YourDatabaseName];
GO

SELECT T.NAME AS TableName
	,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
	,C.NAME AS ColumnName
FROM SYS.TABLES AS t
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
WHERE C.NAME = 'CustomerID'
	OR C.NAME = 'OrderID'
	OR C.NAME = 'OrderDate'
	OR C.NAME LIKE '%Status%'
	OR C.NAME LIKE '%Promotion%'
ORDER BY SchemaName
	,TableName;

 

That should help get you started in preparing the report.