MSSQL database is one of the Windows system based back end database that we are using for some of our in-house Visual Basic Applications. I have encountered some situation that requires me to check if some data from the one table (Alumni table) that is missing from another table (Student table). Logged in to the database console, I was using then the default system database user.

I’ve got difficulty in extracting information from across different schema. In Oracle database, I could just put the schema name before the table name separated by period. In MSSQL database it’s quiet different. Below is a sample of the MSSQL script to read from multiple schema with system user.

select * from [Alumni].[dbo].[Alumni]
where studentid is not in
(select studentid from [SIS].[dbo].[Student])

The syntax would be [Schema Name].[dbo].[Table Name]. It took me sometime to figure out, but finally things worked out well. This works with MSSQL database version 7.0, but I haven’t tested it in other newer versions. I hope this one works the same for other MSSQL database versions.