During the life of any database there will be times when the development makes a change that breaks functionality of an object somewhere else in the database. SQL Server does a good job in some places of making this impossible, or at least really difficult, but in other places there isn't even a murmur as you execute a script that will bring your system processes out in a nasty plague of error messages.

Where it works.
If you try to create a view based on a table or column that doesn't exist then you get told so quite rapidly. Open up a TSQL editor and connect to the AdventureWorks database and run this script to see what I mean:

SELECT   [m].[ID] ,
         [m].[FName] ,
         [m].[SName] ,
         [m].[StartDate]
FROM    [Jack].[Members] AS m

Now, unless you have a Members table in a schema called Jack you will get this error message:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'jack.Members'.
So, this stops you building an object like a view or procedure that tries to reference something that doesn't exist. SQL Server is saving you from yourself and delivering a product that doesn't work.

Where it doesn't work.
Now, let's try something else. Let's create the schema, a table, insert some data and then create a view based on that table, reasonable enough process for a lot of databases. I did that using this :

CREATE SCHEMA Jack
GO
CREATE TABLE [Jack].[Members]
   (
            ID INT IDENTITY
            NOT NULL ,
            FName VARCHAR(20) ,
            SName VARCHAR(60) ,
            StartDate DATE
   )
GO
INSERT  INTO [Jack].[Members]
   ( [FName] ,
            [SName] ,
            [StartDate]
   )
   SELECT TOP 50 -- insert 50 fictitious members 
         [c].[FirstName] ,
         [c].[LastName] ,
         DATEADD(wk, ( ABS(CHECKSUM(NEWID())) % 600 + 45 ) * -1, 
         -- random number of weeks (more than 45, less than 600) 
            GETDATE()) AS startdate
   FROM    [Person].[Contact] AS c
GO
CREATE VIEW [Jack].vGetMembers
         AS
SELECT   [m].[ID] ,
         [m].[FName] ,
         [m].[SName] ,
         [m].[StartDate]
   FROM    [Jack].[Members] AS m
GO

and prove it works using this:

SELECT   [vgm].[ID] ,
         [vgm].[FName] + ' ' + [vgm].[SName] AS [Member Name],
         [vgm].[StartDate]
   FROM    [Jack].[vGetMembers] AS vgm
   WHERE [vgm].[StartDate] > DATEADD(YEAR,-4,GETDATE()) 
   -- members who joined in last 4 years 
GO

OK, now if we assume this gets put in to production and works really well for a few years but then there is a business need to make some changes and the DBA that built the database isn't around or the Junior DBA runs a script in production instead of test . and so on and so on . and the table gets deleted.

DROP TABLE [Jack].[Members]
GO

Now we get this error if we try to reference the view:
Msg 208, Level 16, State 1, Procedure vGetMembers, Line 7
Invalid object name 'Jack.Members'.
Msg 4413, Level 16, State 1, Line 2
Could not use view or function 'Jack.vGetMembers' because of binding errors.
The view has been rendered useless as an object that it relies on is no longer available. How would you go about checking the database for other places where this table was referenced? Well, Red Gate have a feature in their next version of SQL Compare Prompt that will let you scan your database from the Object Explorer tree to check for any such situations. Simply Right Click the database you want to check and select Find Invalid Objects, the UI then shows you all cases of objects that wont work due to a reliance on other objects that no longer exist.

So, if you want to see what relics there are in any of your databases that wont work when someone tries to use them, take a look at SQL Prompt 5 from Red Gate. You get a 14 day free trial of the full product here - http://www.red-gate.com/products/SQL_Prompt/index.htm

Now, as my Mother always told me, don't forget to clear up after yourself:

DROP VIEW [Jack].[vGetMembers]
GO
DROP TABLE [Jack].[Members]
GO
DROP SCHEMA [Jack]