How To Replace Sysindexes And Sysobjects In The Query For Sql Server 2005?
Feb 26, 2008
hi all,
i was using the system tables namely sysindexes and sysobjects in sql server 2000. But now sql server 2005 is using instead of sql server 2000. Since am using sysindexes and sysobjects, too much time is taking for the execution in sql server 2005. So I need to change the query suitable for sql server 2005.
I have read in msdn that the system tables are replaced with corresponding catalog views in sql server 2005. The catelog view for sysobjects is sys.objects. plz check the link http://technet.microsoft.com/en-us/library/ms187997.aspx
Could anyone please tell me how to replace the sysindexes and sysobjects in the query without rewriting the query too much. If I can replacesysobjects with just sys.objects, then it will be very helpful. Is it possible? And what about sysindexes. Any help anyone could give would be greatly appreciated!
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.
I have a SQL Server 2005 database that has a table with a TEXT column. This TEXT column has XML data in it. The length of the XML data in each record in the table is about 700,000 characters. What is the quickest most efficient method to replace a nodes text with another value? I.E., <LogoLarge>aasdfasdfaasadfasdfsdfasdfadsf</LogoLarge> with <LogoLarge>a</LogoLarge>. This table has about 2 million records. Thanks in advance for your help.
How to replace DateTime field with null value in SQL 2005 server I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value create aa @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate
I'm not sure how to use REPLACE here. The Query following REPLACE returns a string whose format is A B C D and I'm trying to convert it to 'A','B','C','D' I'm doing something wrong because query analyzer doesn't like something about the way I've written this.
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''')
SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal, a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS Technicals FROM a_Financials INNER JOIN a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = '2003 Q3') ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal GO
hi,i have entered records using replace functionnow i have to retrieve that records, i have replaced <'> with <`> character, how to write the query to get a record replacing again <`> with <'>
I can't figure out how to replace a single digit entry in my database with a new digit. i want to replace in the alarms.severity all values of 4 with a new value of 2. For some reason the script I wirte won't get past the digit 4 in the statement. Anyone have a script they can let me try?
When you have a table with no indexes you can see the table name in sysindexes and the relevant data in the rest of the fields with an indid of 0. I understand this bit.....but when you have created an index and then you delete it again.... the row in sysindexes just has the tablename and no other data?
Hi all, In a sql query I need to replace 0 (zero) with "Not rated" ...Can some one help me to do this.In short: how to replace a integer value to a string in a query? is it possible?Thanks for the HelpRamesh
SELECT CRDATE, RCTNCCRD FROM creditcardtable WHERE (RCTNCCRD <> '') AND (NOT (ISNULL(RCTNCCRD, '999') = '999')) AND (RCTNCCRD NOT LIKE '%x%') ORDER BY CRDATE DESC
i want to convert any credit card numbers in creditcardtable to an obfuscated format such as:
4332 3423 5423 5428
And convert it to
XXXX XXXX XXXX 5428
HOWEVER, it will only do it if the order is older than 3 months old. Only 90 days/3 months max
how can i do that? when i use where crcdate<90...it gives me error.. can anyone help me?
Hi, I am populating a dataset in .net with output from sql 2005 database. One of the columns in the table is a 'varchar(max)' type. This dataset is then converted to XML using WriteXml and written to a .xml document. But due to the presence of invalid characters, this process errors out. Is there any way using which these invalid characters can be replaced at the database level itself when querying on the table? The error that is produced is as follows: ' ', hexadecimal value 0x1C, is an invalid character. Line 32201, position 924.
I have read at this location-- http://www.swynk.com/friends/achigrik/RowCount.asp that it is better to use sysindexes to get a rowcount. Can I be assured that this is always up-to-date?
It seems my sysindexes table is inaccurate on a nonclustered index. In my case the rowcount (rows and rowcnt) do not match the actual rowcount of the table. The command UPDATE STATISTICS doesn't change the rows or rowcount, adding 'FULSLCAN' won't budge rowcount either.
After I did a dbcc reindex, the number of rows matched, however, upon adding rows in the table both rows and rowcount are out of sync again.
It's a fairly straightforward table, no triggers, no computed fields, only integer, datetime, varchar and bigint columns. There's a clustered index on a bigint column and a nonclustered index on a integer column.
dbcc show_statistics show that the nonclustered index is updated and it's rows and rows sampled match the number of rows in the table (not in the sysindexes-table).
I'd like to know if I'm chasing ghosts here or if there's something very wrong here. What could be causing the counts being inaccurate? Anyone who could shed some light?
hi all Is there any way to remove inconsistencies from sysindexes table. I have already used all the options of the checkdb as well as checktable but invain. thanks in advance
Hi, Please note that I'm having the below problem:
1- when i run "DBcc CheckDB ('DBName') with all_errormsgs" I Get: Could not read and latch page (1:173) with latch type SH. sysindexes failed.
2- then : select * from sysindexes
Gave me: I/O error (torn page) detected during read at offset 0x0000000015a000 in file 'C:DataDatabasesOld_Data.MDF'.
Connection Broken
3- dbcc checktable ('sysindexes') Could not read and latch page (1:173) with latch type SH. sysindexes failed. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 2, index ID 0, page (1:173). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'sysindexes' (object ID 2).
Forth of all, I can't use a backup because it is old. Can i copy system tables from old database to the current one that we are using...
I tried to export and import data from this corrupted database but it would give me errors...
Is there anyway that i can adjust this database. Please Help Urgently....
I need to delete couple or rows from sysindexes, the code worked fine in sql 2000, but 2005 does not support direct update in sys tables any more, what's the work around? thanks.
USE [dbname]
DELETE FROM sysindexes WHERE name LIKE '_va_Sys_%'
I have a table called MessageBoard. It has a column called Messages. A user can type text including any html tags through a text area ans when he saves it by clicking a button, the content typed by the user is saved in the MessageBoard Table (in the Messages) column. So once saved, the html tags are kept intact. If I have to find and replace certain html tags, what kind of SQL Query I have to write? For example I want to find all the <pre> </pre> tags and replace it with <p> </p> tags. How do I do this?
We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.
Just to be clear: column id (int) column name (string)
Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table
Which transformation do I use to replace the value of the id column with this new id?
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.
I am trying to attach a database that was created on another server. I believe the database was created using SQL Server Management Studio Express 2005. Now, I have successfully attached the database in my development environment, which is using SQL Server Management Studio Express 2005. However, when I tried to attach the database onto our production server, which is SQL Server 2000 I received the error: Error 602:Could not find row in sysindexes for database ID 18, object ID 1, index ID 1. Run DBCC CheckTable on sysindexes. In my development environment, I ran DBCC CheckDB on the source database and no errors are returned. I aslo checked the compatibility level under the database properties and it shows: Sql Server 2000 (80) So, this should not be a version incompatibility issue. What is causing the attach to fail on SQL Server 2000? Thanks for any help.
Does anyone know what status 8388704 represents? The table in question is a heap. There are two of these on the table, and they don't show up in EM but are listed in sp_help. They also have weird names associated with their entries in sysindexes "_WA_Sys_CUST_PO_NBR_0F975522" and "_WA_Sys_ORD_STUS_CODE_0F975522"... I've searched everywhere... HELP! Thanks
Can anyone explain why when I look at table using enterprise manager, highlight a table, all tasks, maanage indexes why only 1 index appears and when I look at the same table in sysindexes is says that there are 8 indexes. This is the sql code I executed: select object_name(id), indid from sysindexes where object_name(id) = 'tbh_matter_summ'
Is it possible that there is a problem with the database?
Hi, I got the data currption after run CheckDB and it cannot be repaired: ------------- Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:273) with latch type SH. sysindexes failed. Server: Msg 8944, Level 16, State 1, Line 1 Table Corrupt: Object ID 2, index ID 0, page (1:273), row 9. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 331 and 120. DBCC results for 'abtrepository'. CHECKDB found 0 allocation errors and 1 consistency errors in table 'sysindexes' (object ID 2). CHECKDB found 0 allocation errors and 1 consistency errors in database 'abtrepository'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (abtrepository repair_fast). DBCC execution completed. If DBCC printed error messages, contact your system administrator. -------------- Please help!
