I did
select DISTINCT object name(id)
from syscolumns
where name = Teacher_Number AND
Length = 30
and it gave me this error:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'id'.
Please what I'm I missing?? Thanks for your help
Which field in syscolumns can tell you if that field can
accept null vaules or not in 7.0 there is a column call
"isnullable" that store this information?
All
i have a database where in the syscolumns table.... each record entry for a INDENTITY field in any table the field syscolumns.PRINTFMT has binary / char trash in it... has anyone ever ran in to this and can this be a big problem ?
thanks
kim
Hi all,
I know that if I access the syscolumns table I can select all the column names in the table.
example: select name from syscolumns
NOw what I need to know is how to do a describe on these tables. For example in oracle if I type in (desc syscolumns) it would give me the structure of the table itself. The other thing I need to know is how to get the columns names for a specific table. I do not want all the column names but only for a specific table in that database.
Any thoughts will be greatly appreciated.
sj
what key in syscolumns gives the parent table name from sysobjects?
View 3 Replies View RelatedJust installed 2005 version and need to use the sysTables and sysColumns.
Where are they now? Hidden - how do I turn them on?
Hi, I have run a DBCC CHECKDB and it found 2 Errors in syscolumns. I have run a CHECKTABLE and found the following Error:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 3, index ID 2: Page (1:266) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table Corrupt: Object ID 3, index ID 2, page (1:266), row 12. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 178 and 72.
DBCC results for 'syscolumns'.
There are 6963 rows in 144 pages for object 'syscolumns'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'syscolumns' (object ID 3).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (EMIS.dbo.syscolumns ).
Can anyone suggest the best way to proceed with this problem. I am a little dubious about running REPAIR_ALLOW_DATALOSS on syscolumns, I am unsure of what will happen if sections of syscolumns go missing. I have run DBCC PAGE ('EMIS',1,266,1) but this has given me nothing to work with.
Thanks in Advance,
Dave Shaw.
All my online research has told me how to get info about a table field's data using the system tables, but I can't find anything that tells how to get that table field's data specifically. Could be that it's difficult to explain so difficult to search on but I know there has to be a way to do this and it can't be that difficult.
How do I use the system tables sysobjects and syscolumns to give me the data from a specific field in a third table?
Basically I don't want to know the field type for a tables field, I want to know that field's value.
Let's say I have a table called tblCompanies and that table has 4 fields. idCompany, companyName, companyState, and companyCountry.
How can I return the value as a command parameter for any one of the 4 fields using sysobjects and syscolumns?
If I were writing dynamic SQL I would do something like this:
set @valueToReturn = exec ('select ' + @fieldNameToReturn + ' from ' + @tableToSearch + ' where ' + @fieldToMatchOn + ' = ' + @valueToMatchOn)
But I don't want to use dynamic SQL, I want to use the existing system tables to write a straightforward query. My nonfunctioning/English version of this would be:
give me the value for the field name I send in as a string (@fieldNameToReturn)
from the table I send in as a string (@tblToSearch)
where (sysobjects.name = @tblToSearch) and (syscolumns.name = @fieldNameToReturn) and (@tblToSearch.@fieldnameToMatchOn = @valueToMatchAgainst)
I'm using sysobjects and syscolumns because that's where I can use my variables for table name and column name to link. I just can't figure out how to get hold of my actual data table and the values in it.
Does that make any sense to anyone? I'm sure someone has had to want something like this.
Thank you, thank you, thank you!
I ran the following query in Query Analyzer for a 7 column table.SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BYc.colidThe results were:I_CSD 1X_STE_XML2I_USR_LCK4T_CRT_RCD5I_USR_CRT_RCD6T_UDT_RCD7I_USR_UDT_RCD8If I use the information_schema view (SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_name ='CSD_XML') I get the same results.The problem is that the colids go from 2 to 4 and the colids gothrough 8 when there are only 7 columns.At one time there was another column in the table, but it has sincebeen dropped and isn't there anymore. It seems that the colids insyscolumns did not update when the column was dropped.Is this because of the way I dropped the column? Is there anything Ican do now that it has happened?
View 3 Replies View Related