Powershell script to clean up files from a specific directory

I came across an issue where the sql backup job that I had scheduled was not cleaning up the old backup files from the backup directory. Although I had specified a file clean up parameter of 1 day but I realized the issue when the backup drive ran out of disk space although I made sure I had provisioned double the space requirement on the drive space.

After investigating the issue I realized that the backup job was not cleaning out the old files from the backup directory. I knew I had to troubleshoot the issue on why the sql job was not cleaning out the old files but I was in the middle of a different production outage and did not have time to work on the cleanup job. And the next scheduled backup was going to start in a couple of hours.

As a quick fix I ran this powershell script to quickly clean out the backup drive so that when the next scheduled backup started – it will be able to run successfully with no issues.

Here is the powershell code I used:

$Path = "B:\Backups" # Path where the file is located
$Days = "1" # Number of days before current date
#Calculate Cutoff date
$CutoffDate = (Get-Date).AddDays(-$Days)
#Get All Files modified more than the last 30 days
Get-ChildItem -Path $Path -Recurse -File | Where-Object { $_.LastWriteTime -lt $CutoffDate } | Remove-Item –Force -Verbose

The above code will cleanup all files in the specified directory older than 1 day.
You can run this in Powershell ISE on the server itself or save this as a .ps1 file and run it from the powershell command window.

Hope this helps. Good Luck!

Find a specific stored procedure in all databases

Got a request to find a specific stored procedure in all the databases.

Instead of scrolling thru each database and stored procedures manually to find the stored procedure, here is a SQL script that will search all the databases and list the databases that have that stored procedure. Hope this helps.

--Search for a specific stored procedure in all the databases on the sql instance
    ,@spName VARCHAR(200) = 'name of stored procedure' -- The name of the procedure you are looking for

            SELECT CHAR(10) + ' UNION ALL '           + CHAR(10) +  
' SELECT ' + quotename(NAME, '''') + ' AS DB_NAME '   + CHAR(10) + 
'         , SCHEMA_NAME(s.schema_id)  AS THE_SCHEMA ' + CHAR(10) + 
'         , s.name  COLLATE Latin1_General_CI_AS AS THE_NAME  ' + CHAR(10) + 
'  FROM ' + quotename(NAME) + '.sys.procedures s '    + CHAR(10) +   
' WHERE s.name = @spName 
  AND s.[type] = ''P'''
            FROM sys.databases
            ORDER BY NAME
            FOR XML PATH('')
            ).value('.', 'nvarchar(max)'), 1, 11, '')


EXECUTE sp_executeSQL @SQL
    ,N'@spName varchar(200)'

Get size of all tables in database

Sometimes I receive a request from client to get a list of all the tables and it’s data size.

Here is a helpful sql script that will get the info you need.

Copy the results to an excel file.

    t.name AS TableName,
    s.name AS SchemaName,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    sys.tables t
    sys.indexes i ON t.object_id = i.object_id
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    sys.allocation_units a ON p.partition_id = a.container_id
    sys.schemas s ON t.schema_id = s.schema_id
    t.name NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.object_id > 255 
    t.name, s.name, p.rows
    TotalSpaceMB DESC, t.name

Hope this helps

Disable Foreign Keys

I received a request to export data from a table in a database in Production to a similar table in a database in the Development environment. I used the export/import wizard thru SQL Server Management Studio but my export was failing giving me an error that the data cannot be copied because Foreign Key was present in the destination database. In the past I would just script out a drop and create script for all the foreign keys then drop all the Foreign Keys, do the data export and then re-create the Foreign Keys. After some research online I came across a better option to just disable the Foreign Keys instead of dropping and recreating them.

But first lets understand what is a Primary Key and Foreign Key.

In SQL Server, a primary key is a single field that has a unique value to define a record. Fields that are part of the primary key cannot contain a null value. A table can have only one primary key. Usually the primary key is used as an index but this can vary.

A table can have only ONE primary key and this primary key can consist of single or multiple columns (fields).

Since primary key constraints ensure unique data, they are often called identity columns.

When you designate a primary key constraint for a table, the SQL engine enforces data uniqueness by auto create a unique index for the primary key columns.

A foreign key is a column or set of columns that allows developers to establish a referential link between the data in two different tables. This link helps to match the foreign key column data with the data of the referenced table data. The referenced table is called the parent table and the table that involves a foreign key is called the child table. In addition, if a foreign key references another column of the same table, this reference type is called a self-reference.

A FOREIGN KEY is a field (or collection of fields) in one table, that links to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced table (parent table).

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column in the child table, because it has to be one of the values contained in the parent table.

Based on the developer’s coding standard – usually it is a good practice to prefix with FK_{FK name} and the same goes with Primary Keys being prefixed with PK_{PK Name}

The following SQL query creates a FOREIGN KEY on the “PersonID” column in the Persons table when the “Orders” table is created:


OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,




If the Orders table is already created then use this SQL query to create a FOREIGN KEY constraint on the “PersonID” column:



If you need to name a Foreign Key constraint and to specify a Foreign Key constraint on multiple columns, use this SQL Query:




You can disable a Foreign Key in a table using the Alter Table statement in SQL Server Management Studio. Here is the syntax to disable a foreign key in SQL Server (T-SQL):

ALTER TABLE [your_table_name]

NOCHECK CONSTRAINT [your_fk_name];



The name of the table where the foreign key has been created.


The name of the foreign key that you wish to disable.

The above script would use the ALTER TABLE statement to disable the constraint called fk_inyour_fk_name on the your_table_name table.

After you have disabled the Foreign Key then you should be able to do your data load using the Daa with no error.

To disable all constraints

— disable all constraints


To turn the constraints back on – the print command is optional and it is just for listing the database tables.

Run this:

— enable all constraints

exec sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

To disable the constraints is much helpful when you have to copy data from one database to another. I prefer this then dropping constraints.

If you have triggers in the database then you will have to disable the triggers prior to your data load and then add the triggers back on once the data load is completed.

To disable all constraints and triggers run this:

sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”

sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”

To enable all constraints and triggers run this:

exec sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? ENABLE TRIGGER all”

The word of caution is disabling constraints and triggers – to you have make sure there are no new deltas being written to the database by the users because once you disable all the constraints and triggers any new deltas written to the database that might violate the integrity of the database. Hence you have to ensure that all application traffic is stopped.

Also if you need to import a large amount of data, then consider using BULK INSERT because this method does not fire the triggers. However after your bulk insert it completed, you will need to fix any data integrity issues which occurred during your bulk insert that circumvented the trigger policies.

Hope this helps clarify the concept of Primary Keys, Foreign Keys and Constraints.

Unable to open SQL Server Configuration Manager. When I click on Configuration manager I get the error: Cannot connect to WMI provider

I rdp’d to the sql server and to check which sql services are installed on the server – I tried to open SQL Configuration manager but I was unable to open SQL Server Configuration Manager. When I clicked on Configuration manager I get the error: Cannot connect to WMI provider.
You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.

The first thing I did was to check if I have admin rights to the server.

How to fix the WMI Provider Error:
Open a command prompt in the location machine (run as administrator)
Then run the following command according to the SQL Server version which was installed on the machine.

SQL Server 2005
mofcomp “%programfiles(x86)%\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof”

SQL Server 2008 / R2
mofcomp “%programfiles(x86)%\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof”

SQL Server 2012
mofcomp “%programfiles(x86)%\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof”

SQL Server 2014
mofcomp “%programfiles(x86)%\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof”

SQL Server 2016
mofcomp “%programfiles(x86)%\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof”

SQL Server 2017
mofcomp “%programfiles(x86)%\Microsoft SQL Server\140\Shared\sqlmgmproviderxpsp2up.mof”

SQL Server 2019
mofcomp “%programfiles(x86)%\Microsoft SQL Server\150\Shared\sqlmgmproviderxpsp2up.mof”

SQL Server 2022
mofcomp “%programfiles(x86)%\Microsoft SQL Server\160\Shared\sqlmgmproviderxpsp2up.mof”

If you run the above script for the correct sql version that is installed on your server then you will get the following message:

Hope this helps people resolve the issue they get when they try to open SQL Server configuration manager and get a WMI error.

Grant Users permission to edit SQL Job Schedules

If a non-sysadmin users requests permission to modify sql job schedules that are not the owner of then you can do the following:

Grant the user execute permission to


Here is the TSQL for it:

GRANT EXECUTE ON sp_update_job to [username]
GRANT EXECUTE ON sp_update_jobschedule to [username]
GRANT EXECUTE ON sp_update_jobstep to [username]

I also tried granting the user db_owner to the msdb database. But the user was still not able to edit the sql server job schedule.

The user kept getting this error:

On researching the error code, there seems to be no other option but to grant the user sysadmin permission to be able to edit the sql job schedule for all the jobs on the sql instance.

Only sysadmin role members can edit and run jobs owned by others.

Then I came across this info:

Grant execute permission to these stored procs

GRANT EXECUTE ON sp_update_job to [username]
GRANT EXECUTE ON sp_update_jobschedule to [username]
GRANT EXECUTE ON sp_update_jobstep to [username]

GRANT EXECUTE ON sp_add_job to [username]
GRANT EXECUTE ON sp_add_jobstep to [username]
GRANT EXECUTE ON sp_add_jobschedule to [username]
GRANT EXECUTE ON sp_update_job to [username]
GRANT EXECUTE ON sp_update_jobstep to [username]
GRANT EXECUTE ON sp_update_jobschedule to [username]

GRANT EXECUTE ON sp_help_job to [username]
GRANT EXECUTE ON sp_help_jobstep to [username]
GRANT EXECUTE ON sp_update_jobschedule to [username]

GRANT EXECUTE ON sp_delete_jobschedule to [username]
GRANT EXECUTE ON sp_help_jobhistory to [username]
GRANT EXECUTE ON sp_start_job to [username]
GRANT EXECUTE ON sp_stop_job to [username]

GRANT EXECUTE ON sp_delete_job to [username]
GRANT EXECUTE ON sp_delete_jobstep to [username]

And it worked!! so no need to grant the user sysadmin rights to the sql instance.

Search all SQL objects for a specific text

I was tasked to replace all the db mail profile name to a new profile name in all the objects in a database that uses specific old profile name. This can easily turn into a tedious task to manually open/edit all the sql server jobjects in each database.

I wrote this query to quickly find all the sql objects that use the specific db mail profile name. This will give you are list of all the sql objects in the Results window. Now you simply have to go by this list to edit/update each of these specific stored procs with the new db mail profile name.

WHERE [text] LIKE '%@profile_name%'  
--AND OBJECTPROPERTY(id, 'IsProcedure') = 1  

Hope this helps.

What is SQL Server Availability Groups

SQL Server Availability Groups are a high availability and disaster recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, Availability Groups maximize the availability of a set of user databases for an enterprise. An Availability Group supports a failover environment for a set of user databases, known as availability group or AG databases, that fail over together. An Availability Group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. The number of supported databases varies by which edition of SQL Server you are running – Standard or Enterprise Edition.

High availability Availability Groups can provide automatic failover to a secondary replica if the primary replica fails. This helps to ensure that applications can continue to access databases even if there is a hardware failure or other unplanned outage. This can be used to replicate databases to a remote site, providing protection against site-wide disasters. It can also be used to distribute read-only workloads to secondary replicas, which can help to improve performance and scalability for applications.

Availability Groups can be configured with synchronous or asynchronous replication. So, what is the difference?

Synchronous replication: In synchronous replication, the primary replica waits for an acknowledgment from all secondary replicas before committing a transaction. This ensures that all replicas are always in sync, but it can also impact performance, especially if the secondary replicas are located far away from the primary replica.

Asynchronous replication: In asynchronous replication, the primary replica does not wait for an acknowledgment from the secondary replicas before committing a transaction. This allows for higher performance, but it also means that the secondary replicas may not be completely up-to-date with the primary replica.

When to use synchronous replication:

  • When you need the highest possible data consistency
  • When you can tolerate some performance impact
  • When your secondary replicas are located close to the primary replica

When to use asynchronous replication:

  • When you need the highest possible performance
  • When you can tolerate some data loss
  • When your secondary replicas are located far away from the primary replica
  • When you are using Always On availability groups for disaster recovery

In general, synchronous replication is recommended for production environments where data consistency is a top priority. Asynchronous replication is recommended for development and test environments, and for production environments where performance is a top priority and data loss can be tolerated.

To create an Always On Availability Group, you must first create a Windows Server Failover Cluster (WSFC). Once you have created a WSFC, you can use the CREATE AVAILABILITY GROUP using Transact-SQL (T-SQL) statement to create the AG or thru the SSMS GUI. The CREATE AVAILABILITY GROUP statement specifies the name of the AG, the databases to include in the AG, and the replicas for the AG.

Hope this gives you some important information on SQL Server Availability Groups.

Command Shell (CMD) to get list of all software installed on the windows machine

I had a request from the auditing team to get a list of all the software installed on a windows machine. We can view all the software installed in control panel but I needed to get a text output of the result so I can put it in a word document and send it to the auditors.

I found this cmd shell script that you can run to get this data. Then copy the data from the command window and paste it to any word document, excel or notepad – your choice.

Make sure you open the command shell window in administrator mode.

Get-WmiObject -Class Win32_Product | Select-Object -Property Name, Version, Vendor, InstallDate

Hope this helps.

Grant access to Azure Synapse

Create the user in the master database

--Use Master Database
ALTER ROLE dbmanager ADD MEMBER [user@domain.com] ;
ALTER ROLE loginmanager ADD MEMBER [user@domain.com];
Grant Alter any user to [user@domain.com] with GRANT OPTION

Now select the user database from the db drop down menu in SQL Server Management Studio

--Use UserDatabase
ALTER ROLE db_owner ADD MEMBER [user@domain.com]; --this grants the db permission to the user
Grant Alter any user to [user@domain.com] with GRANT OPTION  --this grants the user permission to modify permissions for other users.
EXEC sp_addrolemember '[schema_name]','user@domain.com' --this adds the user to a specify schema role

Hope this helps you to grant users permission to Azure Synapse Databases.

Database Mail Troubleshooting

Database Mail Troubleshooting

I use the following scritps to troubleshoot database mail issues.

–Get a list of email sent items
SELECT * FROM dbo.sysmail_sentitems

–Another version of the script to get a list of email sent items
USE msdb
SELECT sent_status, *
FROM sysmail_allitems
order by send_request_date desc

–Check to see if broker service is enabled
SELECT is_broker_enabled FROM sys.databases WHERE name = ‘msdb’;

–Check if database mail service is running
EXECUTE msdb.dbo.sysmail_help_status_sp

For SQL 2016 – you will need to have .net 3.5 installed on the server for db mail to work

EXEC msdb.dbo.sysmail_help_configure_sp;

–Get the name of the database profile and account name
EXEC msdb.dbo.sysmail_help_account_sp; –this will show the db mail account name
EXEC msdb.dbo.sysmail_help_profile_sp; –this will show the db mail profile name
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;


There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it is helpful to grant this permission to non-dba personnel if they need to do any performance troubleshooting.

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose sql server problems, and tune databae performance.

There are two different types of DMVs and functions:

Server-scoped DMVs and functions – These require VIEW SERVER STATE permission on the SQL Server level.
Database-scoped DMVs and functions – These require VIEW DATABASE STATE permission on each of the database.

VIEW SERVER STATE is a server level permission that grants non-sysadmin users the ability to view results of Dynamic Management Views.

Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a given database instance at a given time. Since DMVs were introduces in SQL Server 2005 and with each new release of SQL Server, Microsoft has been adding additional DMVs (Dynamic Management Views) to help troubleshoot the performance of the SQL Servers. These objects are helpful to monitor the database server in an efficient and controlled manner.

DMVs come in two subsets – Dynamic Management Views (DMVs) and DMFs (Dynamic Management Functions) and are classified as Dynamic Management Objects (DMOs).
DMVs are like any other views where you can select data from them. DMVs require values to be passed to the function just like any other functions. I will go into the details of DMFs in a separate blog.

If the user does not have sysadmin priveleges or has been granted VIEW SERVER STATE permission and tries to run the following as an example:

SELECT * FROM sys.dm_os_wait_stats

The user will see an error like this one:
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

We run the following query to grant the user the access to the entire SQL Server Instance:


This needs to be run on the master database.
Once the above script is executed successfully then that specific user will be able to view Dynamic Management Objects to do any type of performance troubleshooting.

In the case of a individual database scoped access, a user might have data reader access to the database but when the users runs this query:

SELECT * FROM sys.dm_db_partition_stats

They get an error, because this query is trying to access a DMV in the specific database.

WE need to grant VIEW DATABASE STATE for this.

We run the following query to grant the user the access to a specific database:

USE [DatabaseName]

Once you run the above script then the user will be able to get a result from:

SELECT * FROM sys.dm_db_partition_stats

Now lets talk about the security risks for granting VIEW SERVER STATE permission.
Yes, there is a risk in providing VIEW SERVER STATE to non-dba and non-sysadmin personnel since they aren’t supposed to see this level of intricate database information on the sql instance.
As an example: sys.dm_exec_connections, sys.dm_exec_cached_plans, sys.dm_exec_requests, sys.dm_exec_query_stats, all provide information about execution contexts and plans, and when the user uses these with sys.dm_exec_sql_text() or sys.dm_exec_query_plan(), it will give this user information about the sql code and objects in the databases. This is usually a security violation in some organizations.

The VIEW SERVER STATE permission gives the database user unrestricted access to this information when using these DMVs. Althought they cannot change anything in the database, but they can get detailed database execution info that may not be allowed to view based on the user’s organizational responsibilites. I would analyze this type of access request with a grain of salt and ask some questions as to the security implications of this user requesting access having access to the database execution info. Also the user will be able to use DMV’s to look at queries. If the queries or some query parameters can contain confidential information that the user wouldn’t otherwise be able to see prior to granting this access – allowing VIEW SERVER STATE would allow them to do so (dateofbirth or socialscecurity #s)

Hope this helps clarify the differences between VIEW SERVER STATE and VIEW DATABASE STATE permission grants.

SQL Server build numbers

As a DBA we are asked every so often to check the sql server version on a server. When you look at the version # in ssms it looks like this:

Looking at the above SQL Server 13.0.52012.2 does not really tell me right away what is the version # of the sql server running. The most simplest method to get the sql version is to run in SSMS:


This is what is get:

Microsoft SQL Server 2016 (SP2-CU2-GDR) (KB4458621) - 13.0.5201.2 (X64)   Aug 18 2018 07:38:15   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor) 

The above result tells me that this is SQL Server 2016 with SP2-CU2-GDR service pack applied to it.

I put together this list as a quick reference to list of the build #s and the corresponding sql versions.

SQL VersionSQL Build Numbers
SQL Server 20008.0
SQL Server 20059.0
SQL Server 200810.0
SQL Server 2008 R210.5
SQL Server 201211.0
SQL Server 201412.0
SQL Server 201613.0
SQL Server 201714.0
SQL Server 201915.0

Therefore based on the build # in the screenshot above…13.0.52012.2. The 13 tells me that this is a SQL Server 2016 installation.

I hope this page will serve as a quick reference page for you to confirm the corresponding SQL Version with the SQL Build #

SQL Server Integration Services installation failure – VS Shell Installation has failed with exit code 1638

I recently received a request to install SQL Server 2017 integration services only on a server.
After I mounted the iso media on the server, I started with the SQL Server installation.

Choose the option to install a new SQL Server instance as indicated above.

Click Next to continue…

Accept the Licensing Terms…and click next

Next, select the Integration Services only as shown below

Now once the installation started, it failed halfway during the install and gave me the following error:

VS Shell Installation has failed with exit code 1638.

After doing some research online I came across a few recommendations but the one that worked on me was to download these redistributables.

For x64 Download: https://go.microsoft.com/fwlink/?LinkId=746572

For x86 Download: https://go.microsoft.com/fwlink/?LinkId=746571

Do not try to install install both the 64 bit and the x86 bit on the server. Only choose one.
I downloaded the 64 bit version, installed it, rebooted the server.

After the reboot I restarted the sql installation and Voila! I was able to get SQL Server 2017 integration services installed on the server.

Keep in mind that once the installation is completed, you will have to find the icon for the SQL 2017 Configuration Manager and click on it. If you have multiple versions of SQL Server installed on the server as was in my case – when I opened SQL 2016 configuration manager – I was not able to see the SQL 2017 integration services. However when I opened SQL 2017 Configuration Manager

I was able to see the Integration Services I installed.

Hope this helps. Good Luck on your next SQL Installation.

Find a schema in all databases

So what is a Schema?
A SQL Server database contains multiple objects: tables, views, stored procedures, functions, indexes, triggers.

SQL Server Schemas are a logical collection of database objects. A user that owns a schema in the database are known as Schema owners. There is no restrictions on the number of objects can be in a schema.

If you need to find that default schema for a user – you need to be logged into SSMS with that user and then run the following query:


If you need to retrieve all the schema and their owners in a database, use this SQL query:

SELECT s.name AS schema_name, 
       u.name AS schema_owner
FROM sys.schemas s
     INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY s.name;

On some occasion, a business user will give me a sql script to run on a database that does not have a USE clause in the script and they have no idea on which database it needs to be run on. And in most cases there are 50+ databases on the sql instance.

Here is an example of a script:

Select * from schema_name.table_name

Now usually the schema name is dbo but in some rare occasions they have a unique name for the schema. Since the user does now know which database the sql script on I use msforeachdb to find the schema name in all the databases and then I can find out in which database the schema resides on and then I can run the script the business user gave me on that database and provide the user the information they requested.

Here is the sql script to find a schema in all databases:

--This query will return a listing of all tables that matches a schema name on a SQL instance: 
DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT *
EXEC sp_MSforeachdb @command

This has helped me tremendously in numerous occasion of tedious work on finding on which database the schema resides on. Hope this helps.