Rememba Da Schema

There are loads of great scripts out there on other blogs and elsewhere that help you administer your database and run a tight ship but a lot dont show how to account for the database schema, its not uncommon to have everything in the dbo schema but there are lots of databases I manage that also have objects in other schemas and there are a couple of extra steps to take to get a funky new admin script to help you out in all cases.

Equipment you will need.
We will be using sys.schemas and the "[" and "]" brackets to make our scripts properly useful.

So, if you wanted to see all the columns in your database that are nullable you might run a script something like this:
USE [adventureworks]
GO

SELECT
[t2].[name] AS [Table Name],
[c].[name] AS [Column Name],
[t].[name] AS [Data Type],
[c].[is_nullable]
FROM
sys.columns AS c
INNER JOIN [sys].[types] AS t
ON [c].[system_type_id] = [t].[system_type_id]
INNER JOIN [sys].[tables] AS t2
ON [c].[object_id] = [t2].[object_id]
WHERE [c].[is_nullable] = 1

Now, if you use this list to provide an automated process with object names that its going to do work on it could fail when it tries to use the table and column names in dynamic sql. Especially if, as I do, you have to manage databases that got slung together with table and column names that have spaces in them.

To get around this we need to alter the above script a little:
USE [adventureworks]
GO

SELECT
[s].name AS [Schema Name],
[t2].[name] AS [Table Name],
[c].[name] AS [Column Name],
[t].[name] AS [Data Type],
[c].[is_nullable]
FROM
sys.columns AS c
INNER JOIN [sys].[types] AS t
ON [c].[system_type_id] = [t].[system_type_id]
INNER JOIN [sys].[tables] AS t2
ON [c].[object_id] = [t2].[object_id]
INNER JOIN [sys].[schemas] AS s ON [t2].[schema_id] = [s].[schema_id]
WHERE [c].[is_nullable] = 1

Right, we can now see that all these objects are in different schemas but to refer to a certain column we need to format the data slightly differently. we simply wrap each of the first three columns in square brackets and concatenate them together:

USE [adventureworks]
GO

SELECT
'[' + [s].name + '].[' + [t2].[name] + '].[' + [c].[name] + ']' AS [explicitly referenced column] ,
[t].[name] AS [Data Type] ,
[c].[is_nullable]
FROM
sys.columns AS c
INNER JOIN [sys].[types] AS t
ON [c].[system_type_id] = [t].[system_type_id]
INNER JOIN [sys].[tables] AS t2
ON [c].[object_id] = [t2].[object_id]
INNER JOIN [sys].[schemas] AS s
ON [t2].[schema_id] = [s].[schema_id]
WHERE
[c].[is_nullable] = 1

Now you have a list of all your database objects that can be passed into functions, procedures etc that you use to look after your databases.