Get Table Date Using Sysobjects And Syscolumns. Quick?

May 22, 2007

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!

View 1 Replies


ADVERTISEMENT

Syscolumns Table

Jun 12, 2001

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

View 2 Replies View Related

Table Name From Syscolumns

Jan 23, 2006

what key in syscolumns gives the parent table name from sysobjects?

View 3 Replies View Related

How To Create Sysobjects Table

Nov 13, 2007

Hello everyone.

I'm facing to a serious problem.

I wanted to migrate SQL 2000 -> SQL 2005.

First of all, I tried to attach my *.mdf file to SQL 2000. but It was not only failed but also SQL 2005 failed to recover my *mdf file.

Now, I think my *.MDF file has system tables for SQL 2005 because when trying to attach *.mdf to SQL 2000 engine send the following message :

--------------------------------------------------------------------------
Msg 906, Level 22, State 2, Line 1
Could not locate row in sysobjects for system catalog 'Sysobjects' in database 'rudol.net'. This system catalog should exist in all databases. Run DBCC CHECKTABLE on sysobjects in this database.
--------------------------------------------------------------------------

Also, I tried again to attach the *.mdf to SQL 2005 but SQL 2005 doesn't really works.

Stupidly, I didn't back up the *.mdf file.

How do I attach it to SQL 2005 or recovery "sysobjects" table for SQL 2000?
Please answer.

P.S.
Forgive my terrible English. English is not my mother tongue.

View 2 Replies View Related

[Resolved] Drop Table But It's Still In Sysobjects

Dec 26, 2007

Hey all,

Not sure how I did this, but I dropped a table and it still shows up in sysobjects. This breaks a job that rebuilds indexes because the table doesn't exist. I tried recreating and dropping the table, but the extra entry is still there. Would it be okay to just delete the row from sysobjects? This has happened in a 2000 and 2005 db at the same time.

Thanks for any advice.

View 6 Replies View Related

Table SUPPOSEDLY Missing From Sysobjects

Jan 18, 2008

Reposted from 2000 area per suggestion. The site is running SQL 2000 build 2187.

I've got an 'accidental client' (sister company without a DBA at the moment) who has a problem that I've never seen before. They have a table that SQL claims to not exist in sysobjects. But it does exist in sysobjects as a User Table:

quote:select * from MyTable

Msg 604, Level 21, State 5, Line 1
Could not find row in sysobjects for object ID 55671246 in database 'MyTable'. Run DBCC CHECKTABLE on sysobjects.

dbcc checkdb ('MyDatabase')

Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable2'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable3'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable4'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable5'. Check sysobjects.
DBCC results for 'routingengine_historical'.
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 310 rows in 21 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 839 rows in 20 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 470 rows in 35 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 3 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 145 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 19 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 421 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 4 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

dbcc checktable ('MyTable') with ALL_ERRORMSGS

Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.

dbcc checktable ('sysobjects') with ALL_ERRORMSGS

DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


I've fully verified the correct existence of the table and how it links to the object ID by running statements like:

quote:select * from sysobjects where name = 'MyTable' and type = 'U'
select object_id('MyTable')
select object_name(55671246)

Odd that when selecting data from the table the error refers to the database by the table name. Running checktable on sysobjects shows no errors.

Running sp_help 'MyTable' works and shows the expected results as far as columns, indexes, and such. The table structure is fully visible in Management Studio and can be visually explored. Only when querying data is there an issue.

All tables are owned by dbo.

Ideally I need to recover these tables. If that is impossible, I'd like to remove the tables and cleanly free up all the related data pages.

Any thoughts?

View 7 Replies View Related

Stored Procedure Does Not Appear In The Sysobjects Table.

Jul 23, 2005

Hi,I'm trying to determine with my program whether or not a given databasesupports a given feature set. To do this I'm querying for certain storedprocedures in the sysobjects table and if they are present, making theassumption the database will support the given feature. The problem is Ican't find a certain stored procedure in the sysobjects table, even though Iknow it exists and can see other similar procedures using:select * from dbo.sysobjects order by nameIts as if all of my other stored procedures are in the sysobjects tableexcept this one, the one I'm specifically querying for. Are there certainreasons why a proc won't appear in the sysobjects table? Is this somethingI need to fix?Thanks,Robin

View 4 Replies View Related

Quick Date To Char Convert

Aug 7, 2001

What is the easiest way to convert '3/21/1959' to '19590321' I looked at
the CAST and convert and can't come up with a solution.
Thanks

View 3 Replies View Related

SYSCOLUMNS

Jun 28, 2001

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

View 3 Replies View Related

Syscolumns

Apr 26, 2000

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?

View 1 Replies View Related

Need A Quick Hand Using ORDER BY With Two Fields In The Same Table.

Aug 6, 2004

I have a problem with ordering and I am hoping that someone is able to help.

In my table I have two fields, "requestdate" and "sentdate", and when I display the records I would like to sort by BOTH fields. I want to do this so that the full query is in order by date.

I tried:

ORDER BY requestdate, sentdate DESC

But obviously all that does is order by requestdate (which is NULL or a date) and than it will order by sentdate.

Can somebody tell me how to order by both as if they were the same field?

Thanks!

View 2 Replies View Related

Syscolumns, Urgent!!

Sep 26, 2001

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

View 2 Replies View Related

Where Are SysTables And SysColumns?

Sep 17, 2007

Just installed 2005 version and need to use the sysTables and sysColumns.

Where are they now? Hidden - how do I turn them on?

View 5 Replies View Related

DBCC Problem With Syscolumns

Dec 19, 2005

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.

View 2 Replies View Related

T-SQL (SS2K8) :: Date Comparison In Two Table By Returning Nearest Date Of Table A In Table B

Jun 9, 2014

I am having a problem in creating query for this exciting scenario.

Table A

ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000

Table B

IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000

I would like to return the nearest date of Table B in my table like for

ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000

View 3 Replies View Related

COLID Discrepancy In Syscolumns / Information_Schema.Columns - SQL Server 2000

Jul 20, 2005

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

Sysobjects

Jun 26, 2001

How can you modify the crdate in sysobjects?

View 1 Replies View Related

Sysobjects

Mar 20, 2008

I am not aboe to view the userdefined tables in sql 2005. not sure whether my user dont have permission to view. how to view my user permission or not ..

another one , is there any table to find system table instead sysobjects,information_schema in 2000.

View 2 Replies View Related

Sysobjects Question.

Oct 19, 2001

Any idea what are different types in sysobjects table? How can I seperate views only?

select distinct type from sysobjects

type
----
P
U
S
V
K
D
R

View 2 Replies View Related

Trigger On Sysobjects

Jul 8, 2000

I would very much like to create a trigger on the sysobjects table in my database. When I attempt to do so, I get a permission error. Is there some way I can change the permission setting to allow me to do this?

Thanks.

View 1 Replies View Related

No Replinfo On Sysobjects ?

Oct 3, 2006

HI there , i have recently upgraded my publishers to 2005.

Transactional replciation seems to be running fine.

However in SS2000 i always used to query sysobjects on replinfo to see if a table was replicaed and how it was replicated, 1 = snapshot , 3 = transactional etc.

However after upgrading replinfo on sysobjects is 0 for all my articles, whats up with this ?

Must i use sp_helparticle only now? Also how does the system know if an object is replicated now , i used to think it used the replinfo column on sysobjects, how is this different in 2005?

Thanx

View 3 Replies View Related

Accessing Column Name From Sysobjects

Sep 6, 2001

Hi all,
I have a question about accessing a specific column from the database.
I know when we use: select name from sysobjects would select all the table names and the constraints from the database. I want to select all the column names in each of the table. How can I go about doing this.
Thank you

View 1 Replies View Related

Create Trigger On Sysobjects

Aug 16, 2000

Hi there,

Once in a while, I find down there are some missing objects in production database. Because we share the sa password with couple of developers, therefore, it's hard find down who did it. So, I try to create a trigger in sysobjects table to prevent this problem, however, I keep getting the error message "error 229: create trigger permission denied on object 'sysobjects'"
although I log in using sa. Can someone give me some suggestions how to prevent this from happening beside using trace profiler and also why do I get the denied message when create trigger on sysobject even with sa login.

Thanks in advance

View 1 Replies View Related

Xtype And Type In Sysobjects

May 23, 2006

hi,

can any one tell what is the difference between data in xtype and type columns in sysobjects?

View 1 Replies View Related

'sysobjects' Deleted By Accident

Jan 21, 2008

HI friends,

i have deleted the 'sysobjects' table by accident. then follows the problem.
1. default value constraint not working
2. auto increment constraint not working

is that because of this sysobjects problem.
help me out to fix this problem...

hav fun, njoy
sundaram

View 16 Replies View Related

Missing Tables In Sysobjects

Jul 20, 2005

Hi,I'm using "select [name] from sysobjects where OBJECTPROPERTY(id,N'IsUserTable') = 1" to get all user-defined tables(about 17000tables)and found out there are tables missing in sysobjects.How can I refresh/rebuild sysobjects table?is there a better way to get a list of user-defined tables?thanks,

View 2 Replies View Related

Category Column In Sysobjects

Jul 25, 2007

Everyone,



Is there a reference to describe the relevance of the Category column in the sysobjects table? I'm trying to track down stored procedures that are set to automatically run at startup and the only thing I can find that is consistent about those that are set to autorun is that they have a value of 16 in the Category column. Is this consistent? What else may I glean from this column?



Thanks

Tim

View 3 Replies View Related

Types Of Records In Sysobjects

Jun 29, 2006

I have a question about the xtype field in the sysobjects view. What do these characters mean? I think I've found lists, but are they complete?

What is the difference between FN and FS?

View 4 Replies View Related

If Exists (select * From Sysobjects Where Name = [&#39;mylinkedserver&#39;])

Jun 13, 2000

I would like to know how can i check from SQL statement that

whether LinkedSERVER exists or not like we search a table from "sysobjects"

Is there a way where i can search my LinkedServer exists ?


if exists (select * from sysobjects where name = ['mylinkedserver'])

View 3 Replies View Related

Negative Values For RowModCtr Col In Sysobjects Tbl

Jul 23, 2005

Could anyone, please, explain to me why I have negative values inRowModCtr column in sysobjects table? I have tested that after I updatestatistics the RowModCtr column is reset to 0. But why do I havenegative values in the first place? Thx.

View 2 Replies View Related

Get Information From Sysobjects In Another Database (CLR, MS SQL 2005)

Mar 12, 2006

Hi All,

I've written a scalar function in C# for the MS SQL Server 2005 that has to have access to two databases in one instance of a server ("database1" and "database2"). I got information from database1, calculate the name of the database2 and got information from user's tables in the database2. But when I'm trying to get informatin from "sys.tables", "sys.columns", "sysobjects" I always get an error "System.Data.SqlClient.SqlException: This statement has attempted to access data whose access is restricted by the assembly."

All databases are inside one server, I can run this quieries in SQL Server management Studio - could anybody help me what should I set/change to get metainformation from another database in the same server (in fact I need a list of fields of a curtain table).

Thanks,

Alex Gerasimov

P.S. Atrribute [SqlFunction(DataAccess = DataAccessKind.Read)] is in the applicaiton.



View 1 Replies View Related

Querying Sysobjects For Tables In A Single Schema

Apr 23, 2008

I think this should be a dead easy one.

SQL Server 200.

Can I query to find all the tables in a single schema? I can't for the life of me find this one out!

I'm currently using a dreaded cursor with an exists test to get the results I want but there is surely an easier way!

Cheers,
G

View 12 Replies View Related

QUESTION: Status Column Of FK Constraints In Sysobjects - ????

Jul 23, 2005

Greetings:I have to do a one-off forceful change of some data in a database. Ineed to disable some FK constraints, make the data change, and thenre-enable the constraints.My process will be:ALTER TABLE TABLE1 NOCHECK CONSTRAINT FK_TABLE1_TABLE2UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Problem row'ALTER TABLE TABLE1 CHECK CONSTRAINT FK_TABLE1_TABLE2I wanted to get some evidence that the constraints were back up after Idid the 'check constraint' statements, so I looked in sysobjects.There's a status column for the constraints, but I can't make muchsense of the numbers. Most of the FK constraints that I have notchanged are of status 0, but when I NOCHECK a constraint, it goes to2304. When I CHECK it again, it's 2408.Can someone explain this, and suggest a way to double-check that myconstraints are in fact enabled once I run the CHECK statement?Thanks!!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved