Archive for the ‘sp_MSforeachdb’ Category

How to search for the database name(s) and table name(s) in all databases on the SQL Server instance based on a column name?

Has anyone asked you for help in searching for the database name or table name in all the databases on the SQL Server instance with a column name provided? Sure, it sounds easy enough if you had one user database and it has 3 tables of which contains 5 columns each. If that’s the case, your help is probably not needed and you can go back to enjoying your Mocha Frappuccino. But, we live in a very interesting and practical world, where a SQL Server instance can contain multiple databases and each database contains multiple tables and each table can contain up to 20+ data columns. To look for the database name or table name in all the databases just based on a column name can be similar to finding a needle in a haystack:

Needle_In_A_Haystack

Using the following script to search for all the databases and tables that contains the given column name, your search time will be shortened and hopefully you can get back to enjoying your Mocha Frappuccino before it’s too late.

-- Search for the databases name and/or table name in all databases on the SQL Server instance
sp_MSForEachDB 'USE [?];

SELECT 
    Table_Catalog as DatabaseName
    ,Table_Schema as TableSchema
    ,Table_Name as TableName
    ,Column_Name as ColumnName
    ,Data_Type as DataType
    ,Character_Maximum_Length as MaxLength
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_Name like ''%Products%''' -- replace the keyword Products with the column name keyword you wish to search 

This will be similar to having a big magnifying glass in the search for the needle in a haystack!

Needle_In_A_Haystack_Magnifying_Glass

How to query the databases logical data and log file names, physical path, and space utilization information?

You wish to query all the databases in the current SQL Server instance to find their database names, logical files names, physical path, total space reserved in MB, space used in MB, space free in MB, and % of space free. All these valuable information are stored in the sys.database_files system table. The space allocation and utilization information are stored as data pages. Note that 1 page = 8KB. 128 pages = 1 MB. To convert the data pages over to MB for the space used in MB, space free in MB, you must first use the FILEPROPERTY() function and pass in the logical file name and SpaceUsed as parameters, then divided by 128. Instead of a while loop, we can use the undocumented stored proc sp_MSForeachDB to loop through all databases in the instance. The following script is an example of how it can be done:

-- Querying the data and log file names, physical path, and space info

-- create a temp table to store the databases' files specs info for all databases
IF OBJECT_ID('TEMPDB..#DatabaseFilesSpecs') IS NOT NULL
DROP TABLE #DatabaseFilesSpecs

CREATE TABLE #DatabaseFilesSpecs
(
	ID INT identity (1,1)
	,Database_Name VARCHAR(123)
	,Logical_File_Name VARCHAR(123)
	,Physical_Name NVARCHAR(MAX)
	,TotalSpaceReservedMB DECIMAL(10,2)
	,SpaceUsedMB DECIMAL(10,2)
	,SpaceFreeMB DECIMAL(10,2)
	,PercentSpaceFree DECIMAL (10,2)
)

-- load the databases' files specs into the temp table
INSERT INTO #DatabaseFilesSpecs
EXECUTE sp_MSforeachdb' USE ?
SELECT db_name() as Database_Name
,df.name as Logical_File_Name
,df.physical_name
,ROUND(CAST((df.size) AS FLOAT)/128,2) AS TotalSpaceReservedMB
,ROUND(CAST((FILEPROPERTY(df.name,''SpaceUsed'')) AS FLOAT)/128,2) AS SpaceUsedMB
,ROUND((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,''SpaceUsed''))AS FLOAT)/128),2) AS SpaceFreeMB
,ROUND((((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,''SpaceUsed''))AS FLOAT)/128)) / (CAST((df.size) AS FLOAT)/128) * 100),2) AS PercentSpaceFree
FROM sys.database_files df
'
-- Insert the following between line #21 and #22 to omit the list of system databases
-- IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')

-- query the #DatabaseFilesSpecs temp table
SELECT
	ID
	,Database_Name
	,Logical_File_Name
	,Physical_Name
	,TotalSpaceReservedMB
	,SpaceUsedMB
	,SpaceFreeMB
	,PercentSpaceFree
FROM #DatabaseFilesSpecs

REFERENCES:

FILEPROPERTY()
http://msdn.microsoft.com/en-us/library/ms188401(v=sql.105).aspx

sys.database_files
http://msdn.microsoft.com/en-us/library/ms174397(v=sql.105).aspx