Where Are SysTables And SysColumns?
Sep 17, 2007Just installed 2005 version and need to use the sysTables and sysColumns.
Where are they now? Hidden - how do I turn them on?
ADVERTISEMENT
Replicating SysTables
Apr 21, 2004Good Day Gurus
I posted a question about replicating logins to the database and the answer i got about doing a DTS to transfer logins is not good for me, is it possible to replicate syslogins tables so that i can do this if so, how because they are not listed Database and Publications when i try to create a publication, only individual created databases can be seen.
The reason for this is because when the DBA decides to change the user permission, i want the info to be merge replicated to the subscriber. At this moment i can run the DTS to transfer the login but it won't know when the logins have been updated and hence i won't know when to run it.
Thanks in advance
Querying Systables To Determine Primary Keys
Apr 1, 2004Does anyone know how I could query the systables or perhaps use the information schema views to determine which columns of tables are the primary keys? Any help would be greatly appreciated.
View 4 Replies View RelatedSQL 2012 :: DB Containment And Systables Collation Error
May 5, 2014I have setup db containment with a different Collation that the TempDB and it works perfectly when using temptables. However, when I try to run a script with links a table on my contained db with systable, example sys.allobjects I still get the collation error. I or now added "COLLATE database_default" for the fields which is causing the error on the join. I know we do not link to the the systables a lot so it's easier using db containment where we have a lot of collation issues when working with temptables.
The option is not available to change collation to be the same as the system databases.
Another question I have is, our database is currently using Latin1_General_BIN. Our system db's are on Latin1_General_CI_AS. This DB ontainment is OK and working here. What happens if we add Reporting services db which uses Latin1_General _CI_AS_KS_WS, will it work using our DB collation,with TempDB and Reporting services collation or are we going to run into trouble. I did actually test this and we do get the following collation error when combining Temp tables with our database and Reporting services
Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "Latin1_General_BIN" in the equal to operation.
SYSCOLUMNS
Jun 28, 2001I 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
Syscolumns
Apr 26, 2000Which 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?
Syscolumns Table
Jun 12, 2001All
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
Syscolumns, Urgent!!
Sep 26, 2001Hi 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
Table Name From Syscolumns
Jan 23, 2006what key in syscolumns gives the parent table name from sysobjects?
View 3 Replies View RelatedDBCC Problem With Syscolumns
Dec 19, 2005Hi, 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.
Get Table Date Using Sysobjects And Syscolumns. Quick?
May 22, 2007All 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!
COLID Discrepancy In Syscolumns / Information_Schema.Columns - SQL Server 2000
Jul 20, 2005I 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