Is there a way in MS SQL Server to use a query to get table information (Example: in MySQL, the following query can be used to get the table information describe to you:
Describe [Table_Name_Here];
Is there a way in MS SQL Server to use a query to get table information (Example: in MySQL, the following query can be used to get the table information describe to you:
Describe [Table_Name_Here];
As far as I remember you have to query the system tables. But there is probably a easier way to do it or maybe a system sp. I'm not sure what it is though. try;
Code:select syscolumns.name, syscolumns.length , systypes.name as data_type, syscolumns.isnullable, syscolumns.isoutparam from syscolumns inner join sysobjects on (sysobjects.id = syscolumns.id) inner join systypes on (syscolumns.xtype = systypes.xtype) where sysobjects.name = 'MYTABLE' and systypes.status not in (3, 1) order by syscolumns.colorder
Check out My Awesome Blog & News Jamaica
thats as close as you will get to it, all SQL Server schema is stored as Meta Data in system tables. information on the system tables is documented in help file for Query Analyzer
Last edited by icymint3; Oct 31, 2006 at 07:38 PM.
Cultured in Aggression and Koding like a Warrior!!
“Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
"The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein
thanks, that was a big help, gonna create a Store Procedure to make the task simple.
I'm here wondering if a similar approach can be used for a MS Access Table.
Hmm, back in the heady days of ASP, when Microsoft's ADO was used for database access, there was a much overlooked library that you could use for performing such tasks; ADOX. With it, not only could you pull the schema from a table but actively make changes to it as well.
It was never as well documented as some of the other MDAC components but if you search around you can still find a write-up or two about it (e.g. http://www.4guysfromrolla.com/webtech/013101-1.shtml)
this ADOX is interesting, i am doing a lot of research into it.
I was also wondering if i can be used in VB.Net 2005
What type of information do you want about the table?Is there a way in MS SQL Server to use a query to get table information
Basically what i needed was Column names, data type and Length.
The query that OWEN provide did the trick.
select syscolumns.name, syscolumns.length , systypes.name as data_type, syscolumns.isnullable, syscolumns.isoutparam
from syscolumns inner join sysobjects on (sysobjects.id = syscolumns.id) inner join systypes on (syscolumns.xtype = systypes.xtype)
where sysobjects.name = 'MYTABLE' and systypes.status not in (3, 1) order by syscolumns.colorder
Now is there a way to get the names of the table for a specific Database in both SQL Server and MySQL 5 ?
The reason for me needing this information is I am planning to write a program while generate Visual Basic.NET code for that specific table.
I already wrote one which will generate VB6 code (Insert and Update code) which made life a lot easier for me which writing Database applications.
With, the new version I plan to write, a Class for the Specific table will be generate which should have everything needed to Insert, Update , Select and Delete Statement.
Last edited by girldemsuga; Nov 9, 2006 at 04:10 PM.