INFOMRATION_SCHEMA.COLUMNS question

I have a script that is looking at
IF NOT EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Security_Code_DE'
AND COLUMN_NAME = 'ActiveProductBitMap')
I can see in enterprise manager that the db I restored does not have a
column ActiveProductBitMap, nor should it. The db before restore did. I must
have taken some sequence of steps to fail to update the master db. But I
don't know what that would be, since I know very little about the master db.
How does the master db keep track of schema on restore? ThanksI'm not sure what you asking here...

> I can see in enterprise manager that the db I restored does not have a
> column ActiveProductBitMap, nor should it.
... OK.

> The db before restore did.
Which database? The one you just restore onto? If so, that is overwritten by
a restore operation.
I fail to see what the master database has to do this this. Can you elaborat
e?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephanie" <IwishICould@.NoWay.com> wrote in message news:erbEsWLqFHA.3520@.tk2msftngp13.phx
.gbl...
>I have a script that is looking at
> IF NOT EXISTS ( SELECT *
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'Security_Code_DE'
> AND COLUMN_NAME = 'ActiveProductBitMap')
> I can see in enterprise manager that the db I restored does not have a
> column ActiveProductBitMap, nor should it. The db before restore did. I mu
st
> have taken some sequence of steps to fail to update the master db. But I
> don't know what that would be, since I know very little about the master d
b.
> How does the master db keep track of schema on restore? Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23ix%23jLqFHA.1324@.tk2msftngp13.phx.gbl...
> I'm not sure what you asking here...
>
> ... OK.
>
> Which database? The one you just restore onto? If so, that is overwritten
> by a restore operation.
>
Yes.

> I fail to see what the master database has to do this this. Can you
> elaborate?
These views (INFOMRATION_SCHEMA) are supposed to reflect the schema of the
db. The db guy here is telling me that during a restore SQL server updates
the information in master to reflect the schema of the db as restored. This
is not what I am seeing. Since scripts that run are accessing information in
the INFORMATION_SCHEMA views, the underlying data about the schema should
reflect the schema as it currently exists in the db.
So, is the db guy wrong, and the restore process does not update the schema
info stored in master? Or is there a proper way to do the restore such that
the schema info is reflected properly?
Thanks.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Stephanie" <IwishICould@.NoWay.com> wrote in message
> news:erbEsWLqFHA.3520@.tk2msftngp13.phx.gbl...|||> These views (INFOMRATION_SCHEMA) are supposed to reflect the schema of the db. The db guy
here is
> telling me that during a restore SQL server updates the information in mas
ter to reflect the
> schema of the db as restored.
That makes no sense at all to me. Seems to me like that person doesn't know
SQL Server very well or
that the person didn't explain very well.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephanie" <IwishICould@.NoWay.com> wrote in message news:%239UvRsLqFHA.3136@.TK2MSFTNGP11.p
hx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23%23ix%23jLqFHA.1324@.tk2msftngp13.phx.gbl...
> Yes.
>
> These views (INFOMRATION_SCHEMA) are supposed to reflect the schema of the
db. The db guy here is
> telling me that during a restore SQL server updates the information in mas
ter to reflect the
> schema of the db as restored. This is not what I am seeing. Since scripts
that run are accessing
> information in the INFORMATION_SCHEMA views, the underlying data about the
schema should reflect
> the schema as it currently exists in the db.
> So, is the db guy wrong, and the restore process does not update the schem
a info stored in master?
> Or is there a proper way to do the restore such that the schema info is re
flected properly?
> Thanks.
>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eA23TqMqFHA.2416@.TK2MSFTNGP14.phx.gbl...
> That makes no sense at all to me. Seems to me like that person doesn't
> know SQL Server very well or that the person didn't explain very well.
>
How does one get the master database to reflect the actual schema of a
restored db? I, for one, don't know the internals of Sql Server At All. And
I am having trouble finding anything other than transact sql reference to
this information. Or am I missing the point of these views?
Thanks.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Stephanie" <IwishICould@.NoWay.com> wrote in message
> news:%239UvRsLqFHA.3136@.TK2MSFTNGP11.phx.gbl...
>|||I don't know what schema you refer to. The database schema is contained *ins
ide* the database, not
in master.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephanie" <IwishICould@.NoWay.com> wrote in message news:%23MbBC0MqFHA.2696@.TK2MSFTNGP11.p
hx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eA23TqMqFHA.2416@.TK2MSFTNGP14.phx.gbl...
> How does one get the master database to reflect the actual schema of a res
tored db? I, for one,
> don't know the internals of Sql Server At All. And I am having trouble fin
ding anything other than
> transact sql reference to this information. Or am I missing the point of t
hese views?
> Thanks.
>
>|||> How does one get the master database to reflect the actual schema of a
> restored db?
The master database knows very little about the schema of user databases.
These are stored in the user databases and, in the case of a database
restore, the backup file contains this information, not master. This is why
you can backup a database on one server, and restore it on another (whose
master database, obviously, has absolutely no knowledge of the new
database).
So perhaps, instead of trying to "get the master database to reflect"
anything, you need to describe exactly what you are trying to accomplish.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23kw704MqFHA.2956@.TK2MSFTNGP12.phx.gbl...
> The master database knows very little about the schema of user databases.
> These are stored in the user databases and, in the case of a database
> restore, the backup file contains this information, not master. This is
> why you can backup a database on one server, and restore it on another
> (whose master database, obviously, has absolutely no knowledge of the new
> database).
> So perhaps, instead of trying to "get the master database to reflect"
> anything, you need to describe exactly what you are trying to accomplish.
> A
>
I am looking at a script with
IF NOT EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Security_Code_DE'
AND COLUMN_NAME = 'ActiveProductBitMap')
This condition is returning true. If I do select * from
information_schema.columns... I see a row. If I right click on the table in
Enterprise manager, and select design, I do not see this column. This is a
problem for me.|||> information_schema.columns... I see a row. If I right click on the table
> in Enterprise manager, and select design, I do not see this column. This
> is a problem for me.
And what do you see when you open Query Analyzer, hit F8, go down to the
table, expand, and expand the Columns node? Is your phantom column there
are not?|||> I am looking at a script with
> IF NOT EXISTS ( SELECT *
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'Security_Code_DE'
> AND COLUMN_NAME = 'ActiveProductBitMap')
> This condition is returning true. If I do select * from information_schema
.columns... I see a row.
> If I right click on the table in Enterprise manager, and select design, I
do not see this column.
> This is a problem for me.
Perhaps it is only a matter of refresh in EM? Or could you have two tables w
ith the same name but
different owners?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephanie" <IwishICould@.NoWay.com> wrote in message news:%23hJBdkNqFHA.3724@.TK2MSFTNGP12.p
hx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:%23kw704MqFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I am looking at a script with
> IF NOT EXISTS ( SELECT *
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'Security_Code_DE'
> AND COLUMN_NAME = 'ActiveProductBitMap')
> This condition is returning true. If I do select * from information_schema
.columns... I see a row.
> If I right click on the table in Enterprise manager, and select design, I
do not see this column.
> This is a problem for me.
>