hi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL') and sysstat & 0xf = 3)
drop table dbo.MRDD_FINAL
This code was generated when I used the create a script to build a table from an existing table.
is there a way to check if a a table contains data or not,
The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing...
regards
Ali
Anyone know how to place additional indexes on system tables?
View 1 Replies View RelatedHello
I wonder if I can generate the "CREATE TABLE" sentence given the name of a table by means of a stored procedure.
Thanks a lot.
I'm just beginning to experiment with memory optimised tables.
I have two sets of near identical tables - one set normal, the other set memory optimised with DURABILITY=SCHEMA_ONLY - and am running test queries against these. When I say that the two sets are "near identical", I mean that they are the same except for the primary keys: for the normal tables these are defined as PRIMARY KEY CLUSTERED whereas for the memory-optimed ones they are defined as PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=nnnn) as per the requirements for such tables.
I then run a pair of test queries, again identical but one referencing the normal tables and the other referencing the memory optimised ones.
(The query uses an inner join on three tables with row counts of approx 3m rows, 100000 rows and 5000 rows.)
The query against the normal tables runs noticeably faster than that against the memory optimised ones. To try to find out why, I examined the execution plans. the plan for the memory optimised query suggests that I have a missing index: but of course I can't create this againsty a memory optimised table. Is this a bug or am I missing something? Why the performance between the two should be so different?
I ran a SP that lists fragmented tables in our DB which listed almost all the tables and indexes in the DB. The list includes index names that start with "_WA_Sys_tablecolumn name_.....". Are these SQL Server generated indexes or are these statitics on the tables? What are they? If these are system generated indexes as I thought they were, how are they generated? Can these indexes be dropped or should one want to? IF so, how? Any effect on the DB performance if dropped?
Thanks for your advise.
Helen
Hi,Is het possible to find information about indexes in SQL Server 2000?Information like pad index, fill factor, index fields, SortinTempdb?Gr,Hennie
View 3 Replies View RelatedIf I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
I have come across a user that has error 2525 errors on sysobjects tables throughout their SQL 6.5 system. The ids match and I can identify the index. But as they are system tables I cannot drop or rebuild the indexes.
Any ideas please?
The errors have been occuring for some time, so it is not an option to restore. I would like to avoid having to transfer data to and from another SQL Server.
Many Thanks
Hi,
Can anybody help me how to Stop this 'System Creation Indexes' (Index Name like 'WA%')?.
Is there any method is available to delete the Existing 'System Indexes'?.
thanks,
Srini
Hi,
We have a client that has a large (5Gb) database replicated to 13 subscribers, the publisher is Sql 2005, the subscribers are Sql Express. The publication has as few filtered articles too. I have found that after several months of continuous running Replication Monitor is taking a long time to report history on each subscriber.
Do people tend to rebuild the indexes on the system merge replication tables on production servers, or should the standard replication jobs take care of this?
Thanks for your help
Graham
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 1 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 RelatedI need to find an ERD of MS SQL Server 2000 system tables. Any suggestions where to look?
View 2 Replies View RelatedI need to find an ERD of MS SQL Server 7.0 and MS SQL Server 2000 system tables. Any suggestions where to look?
View 1 Replies View RelatedHi ,
I need help regarding list of all sql server 2005 system tables
Satish
I added a linked server successfully; but the only tables accessible areonly the system tables!I do have all the rights on both servers (Windows & SQL server).All the non system tables are designated as DBO.Strange thing is that I was able to access the non system tables onlyfrom the query analyzer (not from the SQL Manager tree) by using thefollowing script:EXEC sp_addlinkedserver 'LINKED_SERVER_NAME', N'SQL Server'GOSet ANSI_NULLS onSet ANSI_WARNINGS onGOSelect * From LINKED_SERVER_NAME.Database_name.DBO.table_nameGOThank you very muchMike*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies View RelatedWe are in the process of supporting two databases (Oracle 10g, SQL Server 2005) for our application.
I want to know what is the equivalent Tables/Views in SQL Server for the Oracle System tables dba_tab_comments, dba_tab_cols
Thanks in advance
About a year ago we inherited a SQL server (7.0) from another division of our company. The time has come to migrate the SQL Server functionality onto a new machine. Before I do this though, I need to figure out what the initial installation configuration was so I can set up the new server with the same specs. I am looking for a system stored procedure(s) that can tell me the following:
Case sensitivity, accent sensitivity, SQL build (SP), character set, etc. Basically I need to know what checkboxes were ticked during the initial set up of the server.
Any help would be greatly appreciated. TIA.
How do I move a table (test1) to a physical file group, and the table(test1) indexes to another file group. One clustered index and 3 nonclustered indexes. Thanks!!!!
View 4 Replies View RelatedHello List,
I've a customer table and It has two indexes only. But when I view from the query analyser it list ten indexes with the following name:
"_WA_Sys_status_01EAB64E"
I do not know, what does the above index does and i remember, I did not created that. When trying to drop that, It says, Its not an index. I dont know, what to do. Would somebody please advice me on this.
Thanks,
Hi,
Can someone point me to where I can find out if an SQL server table has an index ? I was in enterprise manager and brought up properties for any given table but was unable to find the information.
Thanks,
Jeff
Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?
thanks
Hi,
I have 2 tables with this design: one has ArticleID as primary key and multiple other fields and one has GroupID as primary key and multiple other fields. Each article can belong in multiple groups so I created a new table called articleGroups with only 2 fields: ArticleID and GroupID to show the groups associated with each article. There is a relation between this table and each of the main 2 tables. My question is, in the articleGroup table, does it make any sense to create an Index on ArticleID, GroupID or both? Since the group is needed for each article the Groups will always be queried everytime the article is queried. So, I am not sure if an index is needed?
Thank you,
Regards,
Iulian
Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?
thanks
Hi,
I want to list the table names in a database "mydev_db".What would be the query ?.
I want to run a similar query to find out the indexes,views,stored_procs etc.
Regards,
Copernicus
I need to drop and recreate indexes in some of my tables that are currently been replicated. I am not sure how this will affect my ongoing replication. Will this cause a problem for me? Please help
Bright
I was trying to find out how much space is available in a 2000 db for allocation to tables and indexes. I am trying to find the amount of space that has to be used-up before another allocation is automatically made to the database. I looked at sp_spaceused but BOL is rather sketchy at defining what the numbers it returns really mean. Is the "unallocated space" the value I am looking for?
Thanks,
Michael
I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.
Thanks
Ran
In SQL Server 2000 one could DBReindex every index that exists in a given database. You can do the same in SQL Server 2005. But how can this be done with the new Alter Index command? It does not allow me to pass in a variable for the object. Any ideas on how to get this done in with Alter Index in 2005? Thanks!
This I can't get to work:
DECLARE
@TableName nvarchar(100)
SET @TableName = 'Account'
USE database;
GO
ALTER INDEX ALL ON @TableName
REBUILD
GO
USE RZTQ5OL02
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT Name from sys.sysobjects where type = 'U'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Hi all,
I would like to enable users that do not belong to groups (server roles) such as sysadmin, serveradmin and don't have db permissions such as ddl_admin or db_owner to run some of the system stored procedures (such as sp_addumpdevice sp_configure sp_serveroption ...) and DBCC commands (such as DBCC CHECKFILEGROUP - requires ob_owner or sysadmin permission).
Is it possible to change permissions of SQL Server system stored procedures?
Is it possible to change permissions of SQL Server DBCC commands?
Thanks,
Assaf
I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).
My plan is thus:
+ Take a full backup of the database
+ Set the recovery model of the DB to simple
+ Drop non clustered indexes
+ Drop clustered indexes
+ Remove PKs (wrong data types, too large!)
+ Narrow data types (add new column, update column in batches to old value, rename new column to old column)
+ Add PKs, which will create clustered indexes automatically based on PK ID
+ Create non clustered indexes
+ Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...)
+ Set the recovery model of the DB to Full
+ Ensure everything works OK or better
The DTS Task Copy Server Objects is PAINFULLY slow.
The Copy Table Wizard is fast but generates an unmanagable DTS and does not bring over the indexes.
Any tips or tricks to copy tables, data and indexes and a reasonable speed?
Thanks,
Carl
I would like to create an index on a table from the Database Explorer.
I am using Microsoft Visual C# 2005 Express Edition.
I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.
Thanks!