Hello,
I am relatively new to SQL Server, although I have used Oracle
extensively.
In Oracle, there are system tables that you can query in order to get
a list of all schemas and all the tables in them. Is there a similar
concept in SQLServer? How would I find about the system dictionary
tables?
TIA :-)
In SQL 6.5, what is "system catalogs"?
View 2 Replies View RelatedHey there,
I'm trying to run the following query:
if exists(select * from shistory.dbo.sysobjects where name='STOCK_815')
drop table [shistory.dbo.STOCK_815]
and I get the error:
"Cannot drop table 'shistory.dbo.STOCK_815', because it does not exist in the system catalog."
The table IS there, DOES exist, but I cannot run that query. The only way I can run it is by changing the name of the table to
STOCK_815 like this:
if exists(select * from Stock_History.dbo.sysobjects where name='STOCK_PRE_815')
drop table [STOCK_PRE_815]
Why?
Thx
Hi All,
I have a requirement where in for a given View name, I need to programatically find out all the tables that are used in the view and I also need to find out how each table in the view is linked.
For example, if I create a simple view like
CREATE VIEW XYZ AS
SELECT *
FROM TableA
INNER JOIN TableB ON TableA.ID = TableB.FKID
INNER JOIN TableC ON TableA.ID = TableC.FKID
I need to find out all the Tables involved in the view i.e. TableA, TableB and TableC. I also need to find out how TableA and TableB, TableA and TableC are linked (i mean the join Condition).
Could some one please point me to resources that can help me find the answer to this.
Thanks in advance
BK
I got the following error msg last night, in fact running anything in a query window. My solution was to stop and restart the SQLServer, but not ideal in a production environment. Anyone know what the cause/solution could be?
TIA, Neil
sg 910, Level 17, State 1
Could not allocate a new object descriptor for required system catalog in database '1'. Another database must be closed or objects in another database dropped in order to open this database.
Msg 2812, Level 16, State 4
Stored procedure 'sp_who' not found.
I'm trying to access a System Catalog View (sys.database_permissions, and others) from a table valued function. I need to be able to pass the database name into the function so that I can operate on any database. Typically I'd use dynamic SQL to do something like
INSERT INTO #tempTable
EXEC ('SELECT * FROM ' + @DBName + '.sys.database_permissions')
But of course I can't use dynamic SQL inside of a UDF. I know I could do this using a stored procedure, but I'd need the output to be a recordset that I can query.
Has anyone done anything like this? I think I'm stuck.
We run std 2008 r2. When I deploy and run a pkg from the catalog, how can I get that flat file system log we always instructed ssis to write to when we ran from the command line? I believe it was the /L param . Not sure at this point if i'll use sql agent or somehow employ task scheduler to kick off the pkg.
View 2 Replies View RelatedHi,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 1 Replies View Related"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
Does anyone know what system tables are used when a sp_spaceused is executed?
Thanks in advance
I want to get rid of systemtables or systemstored procedures which displays when ever i creat new database.
How can I get rid of 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
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.
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 1 Replies View RelatedHello,
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
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
Hello all.
can anyone advise me where I can find a relationship diagram for the system tables in SQL Server2000.
Thanks all.
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
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 2 Replies View RelatedHello,
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
Hello,
Is it possible to:
Add a system table using smo (or at all)?
Add new rows to a system table?
Thanks,
Assaf
In SQL mngt studio...what is the system tables folder in the tables folder supposed to be used for? Do I need to use it for anything?
Gater
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
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!!!!
I need to keep track of changes (delete/modify) in sysusers table. SQL Books Online mentionend that SQL Server 7.0 doesn't support trigger on system tables. What options do I have?
LT
Is there anyway to hide system tables in a database so that only user tables are viewed?
Gary
Is there any way to get the DTS Wizard to see the system tables (sysjobs, sysjobschedules, etc..) in the MSDB or Master database?
View 2 Replies View Relatedafter 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
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.
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.
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?
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 1 Replies View RelatedHi,
Is there a way to hide the system tables on EM?
Thanks