February 5, 2004 at 4:54 am
Hi,
I have a need to create T-SQL queries that can be used to return complete and comprehensive schema information for all entities in a given database. In an ideal world this would cover version 7, 2000 and possbily Yukon.
Does any one know if such queries exist in the public domain?
I have used the in-built Information Schema Views and have looked at SQL-DMO but found both of these lacking somewhat.
Many thanks
Ben Fidge
Senior Systems Developer
February 9, 2004 at 8:00 am
This was removed by the editor as SPAM
February 9, 2004 at 3:38 pm
What are the info schema views lacking?
February 10, 2004 at 3:07 am
Steve,
I can't provide an exhaustive list of what missing but take INFORMATION_SCHEMA.TABLES for example. It doesn't return the ID of each table/view returned so, you must uses:
select *, OBJECT_ID(TABLE_NAME) as ID from INFORMATION_SCHEMA.TABLES= 0
And too filter out all the Views returned and just have user tables (non-system or MS tables) returned, I use:
select *, OBJECT_ID(TABLE_NAME) as ID from INFORMATION_SCHEMA.TABLES
where OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
and OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsTable') = 1
and OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsSystemTable') = 0
and OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableIsFake') = 0
The excessive number of calls to OBJECT_ID really hits performance. INFORMATION_SCHEMA views are a part of the SQL 92 standard and haven't evolved to cater for newer stuff added since it's release.
The above query doesn't even return any information about full text catalog and index associated with each table either.
Hope this helps,
Ben
February 10, 2004 at 8:26 am
Go to the Master DB and look at the INFORMATION_SCHEMA views. Alter whatever you need to and save the query as YourCompany_SCHEMA.
Now you have whatever you need. If needed, move the View to other DBs using the Import/Export Tool.
February 10, 2004 at 9:40 am
I've tried doing this via Enterprise Manager and alway get errors. Have you managed to do this successfully?
Ben
February 10, 2004 at 10:17 am
Can you view the INFORMATION_SCHEMA views
February 10, 2004 at 11:56 am
I can view them in the treeview on the left of Enterprise Manager fine, but double click on any of them (TABLES) and get the following message:
"Error 8181: Text for 'syscomments' is missing from syscomments. The object must be dropped and re-created before it can be used.
Line 1: Incorrect syntax near 'AS'.
Location: colnames.cpp:4212
Expression: ptrCC
SPID: 51
Process ID: 1548
Description: Bad error handling downstream of PtrGetCmd!!
"
When observing via Profiler, with the following query:
select * from INFORMATION_SCHEMA.TABLES
it simply shows that query.
Any ideas,
Ben
February 10, 2004 at 12:02 pm
Do you have sysadmin priviliges? If not, as someone with sysadmin to try.
Also, try this... although I don't think it will work.
Use Master
EXEC sp_Helptext 'Information_Schema.Tables'
February 10, 2004 at 12:12 pm
I'm sure I do have sysadmin priveleges. How do I tell?
I use Windows authentication and my user profile has Admin priveleges system wide.
Tried your suggestions. Get the follwoing error in Query Analyzer:
"[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
This is odd because everythings running on a single desktop machine!
Ben
February 10, 2004 at 12:23 pm
You've got me stumped... but that doesn't take much. Hopefully someone with more knowledge that I will help you.
Good luck and sorry I couldn't help.
February 10, 2004 at 2:06 pm
Well thanks for the help anyway, I appreciate it.
Ben
February 11, 2004 at 12:37 am
Can you publish the exact contents of this queries / views that you are aiming to have in this forum?
This would help, if anyone has got a query written for simmilar purpose. I also have my own set of stored procedures / functions which I use to query table metadata.
So knowing the requirements / idea would certainly help.
-- Amit
February 11, 2004 at 2:38 am
Amit,
Please refer to my post at the top of the page on the same topic at:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=98818&p=2
I'm still compiling exact functional requirements, although in short I would describe this as:
"the ability to retrieve complete schema and metadata information regarding every object type in a given Sql Server 7/2000 database"
My complete list of objects are as follows:
Check Constraint
Column
Database
Default
File
Extended Property
File Group
Key
Full Text Catalog
Index
Permission
Role
Rule
Stored Procedure
Table
Trigger
User
User Defined Datatype
User Defined Function
View
If there's anything I've missed, please let me know. For each of these objects, I want to build queries that would retrieve ALL known flat (non-relational) meta-data information.
For example, Tables requires ID, Name, CreationDate, Owner, FullTextCatalogName, FullTextIndex, FileGroup, ClusteredIndex, PrimaryKey, IsAnsiNullsOn, IsQuotedIdentOn etc etc. Relational meta-information such as columns, indexes, triggers etc will be extracted with different queries and attributed to their parent object in code.
Thanks
Ben
February 11, 2004 at 2:56 am
As per your requirements, Tables requires ID, Name, CreationDate, Owner, FullTextCatalogName, FullTextIndex, FileGroup, ClusteredIndex, PrimaryKey, IsAnsiNullsOn, IsQuotedIdentOn
Just to check, You are thnking of a queries such as
select ID, Name, CreationDate, Owner, OwnerId, FullTextCatalogName, HasFullTextIndex, FileGroupName , HasClusteredIndex, HasPrimaryKey, HasIsAnsiNullsOn, HasIsQuotedIdentOn from TABLEINFO
Which would give you all information about a table in a single row.
-- Amit
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply