System Tables
Do you know where the system tables for a database are stored ? On which
file ?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Revoke Perms On System Tables, Merge System Tables
I have recently set up Merge Replication and it puts alot of extra system tables in the database. When I connect through an ODBC using Access 97 I don't want non-admin users to see these tables (i.e., developers). If I revoke select on say INFORMATION_SCHEMA tables, dbo.sys... tables and dbo.Merge... tables from public, will replication be affected?
View Replies !
View Related
Determining What Are &"system&" Objects In Sp_help Or System Tables
Hi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
View Replies !
View Related
System Tables
I dont want to see these at all and cannot find where tospecify a view or modification of a view. can someone throw me abone here and tell me where to make the adjustments if I can make themat allMichael
View Replies !
View Related
SQL System Tables
Where can I find a discussion of how to interpret the keys1 and keys2 columns of the sysindexes table? These columns are binary(255) and are supposed to contain a description of the columns to which the index applies. I also need to know how to interpret the status column of the sysconstraints column.
View Replies !
View Related
BCP In System Tables
Hello, I have succesffuly BCP out system tables(sysdatabases, sysdevices, syslogins, and sysusages). Now I want to BCP those same tables IN to create devices, databases and so on using the BCP utility. I run the same command used to BCP out and only changes the OUT to IN but it did not work. Can somebody whose done this before tell me how to do it. Many Many thanks, Ana.
View Replies !
View Related
System Tables
Hello, I've one Sybase DBA asking this question, Is this really true, Pls help me. Due to architectural changes in SQL Server 7.0, you cannot dump the transaction log if a database's system tables are inaccessible (due to media failure, for instance)in 7.0 and later versions. Microsoft recommends that separate devices be used for system tables and user tables to allow a final log backup to take place in the event the data device is inaccessible. The additional exposure incurred by not doing this is possible loss of data for the interval between the last transaction dump and the point of failure. Thanks Qinglee
View Replies !
View Related
System Tables
After upgrading to 7.0 any users that connect to any database via ODBC can view the system tables in the user database. I know I can register the server in my installation of Enterprise Manager and set the option "do not show system tables" ,if I fail to do this, does this enable all system tables to be viewed via any ODBC connection ? I asked Microsoft and they advised to remove the users from the public role and revoke permissions from the users on the system tables (ya okay, anyone agree with this ?) So, is anyone experiencing the same situation or does anyone have any advice ? Thanks Clea Boe
View Replies !
View Related
System Tables
Hi All I want to know where I can find Object permission detailes, For example I have table1 and user "abc", I have given permission to user "abc" for table1 to "SELECT" "INSERT", "UPDATE", I want to know where this detail is stored in system table. I was looking at "SYSUSERS","SYSLOGINS", "SYSPERMISSIONS", "SYSMEMBERS", "SYSOBJECTS" I couldn't find anything, If some one can post your answers that will be great. Thanks in Advance Regards Ram
View Replies !
View Related
Can Only See System Tables
Hello, I have created a linked server XYZ that is linked to server ABC. I am tying to view the tables via XYZ but I'm unable to do so. I can only see the system tables. When I run a select statement, I get the correct results. That means I have the access to the tables, yet why am I not able to see the tables. Please assist
View Replies !
View Related
System Tables
Hi, I'm doing an impact analysis to see what views / stored procedures etc are impacted by a change to columns in a table. I'm using the system tables to identify these changes, and it is something that I'll have to repeat across all our databases in case the tables are being accessed from elsewhere. I'd like to make sure I'm doing it the right way so could someone point me in the direction of a good article or code for doing this. thanks Sean
View Replies !
View Related
System Tables
Hi Gurus, select * from sys.server_principals is not working for me on 2000 SERVER. This works fine on 2005 SERVER.. Where Iam going wrong ..please advice.. Thanks, ServerTeam
View Replies !
View Related
Recreate System Tables
Hi!I need to recreate the system tables (sysobjects, syscolumns,sysindexes, sysforeignkeys, sysconstraints, sysreferences, sysindexkeysat least) in another SQL server.You may say "Thats easy! Backup and restore the database!" and I wouldanswer "I can't, the database size is above 50GB and I just can't do itevery time I need to recreate the info".So, ¿Do you know any simple way to do these?I don't need the data on the user tables and I'll log-in always as dbo(using trusted connection), perhaps that helps.Perhaps the simplest way is to do a bcp to backup and restore the databut.... you never know what would happen. This is why I write ask youguy, probably you know much more than me.Thanks in advance
View Replies !
View Related
System Tables In MS SQL Server 5
I am using MS SQL Server management studio.I treid to see some system tables which are sysobjects, syscolumns,systypes, etc.., but i don't see the list under the system tablesfolder. There is one table showing, sysdiagrams; however, I was ableto query sql stmts though.where are they located? or Do I need to contact a dba to release thosetables? thanks.
View Replies !
View Related
System Catalog Tables?
Hello,I am relatively new to SQL Server, although I have used Oracleextensively.In Oracle, there are system tables that you can query in order to geta list of all schemas and all the tables in them. Is there a similarconcept in SQLServer? How would I find about the system dictionarytables?TIA :-)
View Replies !
View Related
Updating System Tables
Hi,I want to replicate the system tables of the Northwind database inanother test database that I have created (say NorthwindTest). Istarted with the syscomments table:insert into NorthwindTest.dbo.syscommentsselect * fromNorthwind..syscomments whereNorthwind..syscomments.id not in(select id from NorthwindTest.dbo.syscomments )On doing so,I get the following error:Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not matchtable definition.Does any one know why the two tables are different?Also, if I try to insert on a per row basis, the following worksinsert into syscomments values(53575229,0,1,0,0x280030002900)but the following DOES NOT workinsert into syscomments values(53575229,0,1,0,0x280030002900,NULL,NULL,NULL,NULL,NULL)FYI, the definition of syscomments table is:CREATE TABLE [syscomments] ([id] [int] NOT NULL ,[number] [smallint] NOT NULL ,[colid] [smallint] NOT NULL ,[status] [smallint] NOT NULL ,[ctext] [varbinary] (8000) NOT NULL ,[texttype] AS (convert(smallint,(2 + 4 * ([status] & 1)))) ,[language] AS (convert(smallint,0)) ,[encrypted] AS (convert(bit,([status] & 1))) ,[compressed] AS (convert(bit,([status] & 2))) ,[text] AS (convert(nvarchar(4000),case when ([status] & 2 = 2) then(uncompress([ctext])) else [ctext] end))) ON [PRIMARY]GOThanks in advance,TC
View Replies !
View Related
System Tables Fragmentation
can you defrag system tables. They appear in my dbcc showcontigreport. Some are worse than other but if it where a user table Iwould defrag it. However the reindex commands doesnt work on systemtables.... Any ideas.Thanks Matt.
View Replies !
View Related
Identity In System Tables
does anyone know how sql 2k stores whether a column is an identity column? is it in any system tables or information_schema? i've read that i can't find the next value of the identity, which i find weird. I just want to know if the column is an identity or not. Thanks in advance.
View Replies !
View Related
Question On System Tables
I can't see the system tables when I try to pull them up in a linked table view from ACCESS or in a VIEW in SQL Server. I can't adjust my permissions to make them appear in any way that I have tried. Do you know how to do this?
View Replies !
View Related
Get Row Count From System Tables
Does anyone know how to get the row count of a user table by using the system tables. There is no guarantee that these user tables will have any indexes - so I can not use the sysindexes table to count the rows in a clustered index. Is there another way?
View Replies !
View Related
System Tables And More - How To Hide Them?
Hi everyone, I've recently installed MSQL Server 7.0 Standart Version and every time I create a new database, system tables are created and are visible also. That means, I instantely get 18 tables, 20 views, 18 stored procedures and so on. Can I hide them??? I mean, my tables are all mixed with the system tables. If I can hide all the system tables and views and so on please tell me! Thanks.
View Replies !
View Related
System View And Tables
I have been querying the sysobjects table to produce a list of user database tables and views. In 7.0, my list is now including the INFORMATION_SCHEMA views and also certain other system tables and views. I have filtered out the INFORMATION_SCHEMA views by looking for 'dbo' objects only. I am having trouble removing some of the system tables and views, such as dtproperties and syssegments. sysobjects shows a type = 'U' while double clicking on tables or views from Enterprise Manager shows these as system objects. What other piece of information can I include in my query to filter out such tables as dtproperties or views such as syssegments.
View Replies !
View Related
Triggers Against System Tables SQL 7
I'm thinking of building a trigger against a system table(sysobjects) in database(a) on server(a) that will assist me in updating a table in database(b)on server(b). What I need to know from table(b)is if a new table has been added and removed from database(a) on server(a). I want to use a store procedure to query the table(b). I was thinking of building a trigger against my sysobjects table that would update table(b) whenever any tables are added or removed from database(a). Has anyone built triggers against system tables? I am running SQL 7.
View Replies !
View Related
How To Defrag The System Tables.
I just ran some dbcc showcontig against some system tables in my production databases in sql 7.0 and found out these tables are heavily deframented. Is there a way to defrag these tables other that using dbcc dbreindex which will not work on system tables. Thanks, Jim Zhong
View Replies !
View Related
Corrupt System Tables
Hi, Recently I found some thing very strange with SQL Server 7.0. My application failed to execute some stored procedure and came up with error like "Expecting '2x' parameters , supplied only 'x' parameters". I used sp_proc_columns to find out the no of parameters and found there are twice the no of parameters for each stored procedure. Then I tried to drop and recreate all the stored procedure. But still all the 'orphan' entries stayed in the database. Does any body came across this problems before? Does anybody know what is the cause? thanks in advance Jacob
View Replies !
View Related
System Tables Rights..
I create a new database and a new user for it and give him db_datareader, db_datawriter and db_ddladmin roles. Other than this, Im denying permission on select, insert, update, execute and delete on all the system tables in his particular database. Will this denying of access to the system tables cause any problem while his is trying to insert or update or do anyother DDL activity from the database. Parasu
View Replies !
View Related
System Tables (very Very Urgent)
Hi everyone, When one of the programmers tried to delte a view from the database there was an error saying the specified view is not present in System catalog. When i tried to see what i can check if its information is stored in any system table i found sysremote_catalog and some more similar tables when i tried to access the table from EM it asked for server name i went back to t-sql and gave the server name it gave an error Server: Msg 155, Level 15, State 1, Line 4 'servername1' is not a recognized OPTIMIZER LOCK HINTS option. Either i can open any other tables similar to that of this one starting with sysremote..what is the problem or is it that we can't look at the information in this tables at any instance of time. Where can i find the information regarding which i can access and what not in system tables and how i can utilize them.. I am in desperate need of help...i appreciate ur time. Thanks in advance. Kavitha.
View Replies !
View Related
System Tables (Urgent)
Hello, I've one Sybase DBA asking this question, Is this really true, Pls help me. Due to architectural changes in SQL Server 7.0, you cannot dump the transaction log if a database's system tables are inaccessible (due to media failure, for instance)in 7.0 and later versions. Microsoft recommends that separate devices be used for system tables and user tables to allow a final log backup to take place in the event the data device is inaccessible. The additional exposure incurred by not doing this is possible loss of data for the interval between the last transaction dump and the point of failure. Thanks Qinglee
View Replies !
View Related
System Tables,Urgent!!
HI all, I need to understand a little bit more about SQL Server built in tables. For example in Oracle if I need to check for tables names under one database. The query will be something like this: Select table_name from user_tables; Or to get column_name and table_names: Select column_name, table_name from user_tab_columns; How can I do this in sql server 7? I know there is a table called sysobjects. The sysobject will give me all the objects in the database but how can I specify wether its a table or a column. More over can someone refer me a good book for sql server 7. Both development and administration. Thank you in advance!!!!
View Replies !
View Related
System Remote Tables ?
after upgrade sql from 6.5 to 7.0 , there are some new system table in system database , all are begin with sysremote_.... such as sysremote_tables, i can not find any information about them , does anyone know what these tables for and whether these tables will affect the new sql7.0 application database ? thanks in advance . regards zg
View Replies !
View Related
System Tables Are Not Visible At All, Why?
Dear all, When you go to Sql Management Studio->Object Explorer and select Tables and then System you don€™t see any at all. Why are not visible system tables for each user database as previously did Sql Server 2000 ? They are hidden or there is other stuff? Any thought or view would be very appreciated.
View Replies !
View Related
Remove System Tables
I made a copy of our database and restored it to a new database. We want to strip all the content out except the users and roles. I have been able to delete everything except "system" tables, views and stored procedures. Can anyone help me get rid of these? Thanks - Kelli
View Replies !
View Related
2005 System Tables: Where Are They
In previous versions we could query the system tables to get metadata. In SQL 2005 they seem to be hidden. They are there. You can query them, but I can't find them. Question: Is there any way to see them in SSMS? Yeah, I know you aren't supposed to, but hey, they are usefull and I like them. Microsoft has given us a confusing collection of alternatives for finding metadata; System views, Catalog Views, Dynamic Management Views, Compatibility Views, Information_schema views. I have tried to organize some thoughts on what these are and here is what I make of them. If any of this is in error, I would like some corrections. SYSTEM VIEW - the stuff below and more. Shows system information. ...CATALOG VIEW - is a system view on the system tables ...DYNAMIC MANAGEMENT VIEW - is a system view on system tables and various buffers for current status info. ...COMPATIBILITY VIEW - is a system view which shows ONLY 2000 information. It is there for compatibility. ...INFORMATION_SCHEMA VIEW - each database has it's own set. Holds db-level metadata. Hayden
View Replies !
View Related
How To Write Triggers On System Tables?
How can we write triggers on system table dbo.sysobjects, when i tries to write a trigger, it is giving an error that permission is denied. I even give a permission of "allow modifications to system catalogs" in Enterprise manager. still it is not giving permission. How can i create a trigger on dbo.sysobjects table?
View Replies !
View Related
Removing System Replication Tables.
Hi, i restored a backup from a database thas has replication configured.When i restored it the system tables that the merge replication createsare restored too. I was investigating on internet and I found that i candelete it using this query:sp_configure 'allow updates', 1goreconfigure with overridegoDROP TABLE aonflict_SiacDataEEC_security_info...sp_configure 'allow updates', 0goreconfigure with overridegoDo somebody know if i use this queries to delete this tables i candamage the database or is correct to use it.Thanks a lot for your help.*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
View Related
Foreign Keys On System Tables
I know altering the schema of system tables is a big no-no, but I was wondering if setting up a table that has foreign keys pointing to a system table is bad. Basically what I'm refering to is in some cases I have CreationDate and CreatedBy fields in my tables that correspond to GETDATE() and USER_NAME() functions in insert statements....I want the CreatedBy field to be a valid SQL server DB username ... and not some unchecked string value (SYSNAME actually)
View Replies !
View Related
|
TRACKER
