Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem Solving With Information Schema Columns Expand / Collapse
Author
Message
Posted Sunday, August 31, 2003 12:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, Visits: 51
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jsack/problemsolvingwithinformationschemacolumns.asp


Post #15814
Posted Thursday, September 18, 2003 5:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, Visits: 51
From the Author: My apologies for the Transact-SQL formating. The code examples work fine - they just look a little strange.




Post #77334
Posted Thursday, September 18, 2003 7:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 21, 2010 7:15 AM
Points: 77, Visits: 10
quote:
Information Schema Views were introduced in Microsoft SQL Server 2000


No, no, no, INFORMATION_SCHEMA views are hidden in SQL 2000, but were clearly browsable with Enterprise Manager back in SQL 7. INFORMATION_SCHEMA is supposed to be some sort of a standard for accessing database schema. Now it is pretty hard getting the names of INFORMATION_SCHEMA views. And if you look at their sources you can find some strange ways Microsoft uses system tables. Looking at view sources you can clearly see things that are not implemented or implemented differently in SQL Server.




Post #77335
Posted Thursday, September 18, 2003 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, Visits: 51
I'm sure with Yukon we'll learn if this (Information_Schema views) was an idea that will die on the vine. In the meantime, Books Online indicates that this is the preferred method. We'll see:

"Information Schema Views
Microsoft® SQL Server™ 2000 provides two methods for obtaining meta data: system stored procedures or information schema views.



Note To obtain meta data, use system stored procedures, system functions, or these system-supplied views only. Querying the system tables directly may not provide accurate information if system tables are changed in future releases.


These views provide an internal, system table-independent view of the SQL Server meta data. Information schema views allow applications to work properly even though significant changes have been made to the system tables. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA.

SQL Server supports a three-part naming convention when referring to the current server. The SQL-92 standard also supports a three-part naming convention. However, the names used in both naming conventions are different. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database. This table describes the relationships between the SQL Server names and the SQL-92-standard names.
"




Post #77336
Posted Thursday, September 18, 2003 11:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1
Hi there

Nice article, ive used similar code before to locate email columns etc when organisations change email address structures etc, all very handy indeed and damn site easier than sys tables :)

Have a good one!

Cheers

Ck

Chris Kempster
www.chriskempster.com
Author of "SQL Server 2k for the Oracle DBA"



Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #77337
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse