Tables in SQL 2

[puterkrazy]

How can you programmatically check for the existance of tables and columns (and data type, length, etc) in that table on a database using SQL?

 

[Sabnac]

Use Sysobjects, syscolumns, systypes, ... system tables or associated views (INFORMATION_SCHEMA.)

 

[JohnDTampaBay]

The INFORMATION_SCHEMA views are the way to go. Microsoft has been threatening to change the structure of the system tables for a while now. They assure us that the views will not change (I feel better already ). Metadata functions such as OBJECTPROPERTY can also be used to return system information.

Just my two cents. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[puterkrazy]

can you tell me more about the information_schema and how to use it?

 

[JohnDTampaBay]

INFORMATION_SCHEMA views are a set of views that provide meta data of system objects (tables, columns, procedures, etc.). There are several views provided with SQL Server, each specific to an object. You can see the complete list under the master database from Enterprise Manager or Query Analyzer's Object Browser. Even though you can't see them in other databases, they are available.

Try the following code from any database you like:

SELECT * FROM Information_Schema.Tables

You should see some interesting information on the tables in the current database.

See information schema views in BOL for a complete explanation. I hope this helps.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[puterkrazy]

thank you! i have another question...
i am getting the error: "invalid object name db.dbo.t1" when executing the following:
select * from dbsrv.db.dbo.t

 

[JohnDTampaBay]

The error references db.dbo.t1 while the SELECT statement references db.dbo.t. Assuming that's not a typo, I would say that maybe t is a view that references a table or view named t1, which no longer exists.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[puterkrazy]

so it seems that dbo is actually "dbo" and not the dbo name? why is this?

 

[puterkrazy]

I also get the following error:
The object name 'DBSRV.db.dbo.Information_Schema.Tables' contains more than the maximum number of prefixes. The maximum is 3.

when executing
select * from dbsrv.db.dbo.Information_Schema.Tables

 

[JohnDTampaBay]

A four-part name consists of ServerName.DatabaseName.ObjectOwner.ObjectName. When referencing these views, Information_Schema is considered the object owner. Since these views cannot be referenced across servers, a server name is not necessary. Try this:

select * from db.Information_Schema.Tables

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[puterkrazy]

I think I may have to use sysobjects, etc. then because I also need to access info on another server.

One more question...
is there a way to test existence of a database on a server with SQL?

I greatly appreciate your help!

 

[JohnDTampaBay]

DATABASEPROPERTYEX(DatabaseName,'IsAnsiNullDefault')

If this function returns NULL, then DatabaseName does not exist. Check BOL for other meta data functions that may be helpful.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[medic]

Very useful. A star for your posts.