I am in need to generate script of all the indexes in a particular database in the format "if not exist ....create index" format.I tried to google this I get for missing indexes, fragmentation level all that extra stuff, I need only in the format of "The script should be in "if not exist ....create index" format for a single database.
View 6 RepliesIf i create a simple table with a foreign key constraint, does itcreate an implicit index on that given ID? I've been told this isdone in some databases, but i need to know for sure if SQL Server doesit. Has anyone heard of this before, on any other databses perhaps?Heres an example of how the foreign key constraint is being added:ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])My initial testing seems to indicate adding an index on the foreignkey column helps, but i need to know for sure. Any insight would begreatly appreciated!Bob
View 6 Replies View RelatedScript they use to generate indexes in SQL 2005.
I have 2 databases on a separate instance. I want to script out all indexes from database1 then execute it on database2.
How to accomplish this task efficiently.
This script generates a format file for every table in a database.
It's set up for fixed width files, not too common in this world...but they can easily be modified.
Also the do not at this time handle text or image columns.
Any hints/advice here would be great.
I'll post it when I figure it out.
happy bcping..
SELECT FORMAT_CARD FROM (
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ char(9)+'"
"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping
Brett
8-)
SELECT @@POST=NewId()
That's correct! It's an AlphaNumeric!
Hello Group
I am trying to create indexing structure for my table in SQL Server 2005. For the way i wanted the indexing I cant use the "create Index " statement. Does anyone tell me how to create indexing strctures from scratch ( like using select statements etc ) or any other ideas. waiting for ur replies guys.
Thank You
Rishi Kanth
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
Hi,
I want to create a empty database with a existed database.
I create all the tables with script that is generated with takes -> generate script command.
I do not know how I can cope the existed database diagram to the empty database.Please give me a idea.
Thanks for your help.
Mark
How can I drop all the indexes in all the tables in a MS 2000 SQL Server?
View 9 Replies View RelatedWorking with sql server 2005. Is there a way through Management Studio to show all indexes within a db?
thanks.
First, I'd like some help stablishing if it's a good idea or not what I'm thinking, and second is what would be the easyist way to achive the result.
Background. We have a multicompany ERP system (200GB, 200+ tables). All data tables have a companyid, counter1id, counter2id, fields. Each table has a primary clusterd index on these fields in that order. And also has multiple other indexes that usually include companyid filed first. ( Note we have 10 companies, ids are 1..10, companies 2 and 5 are the most active, inserts, selects ). MY GUESS IS THAT THIS IS VERY BAD HAVING COMPANYID FIRST. AM I CORRECT? LEADS TO AL LOT OF FRAGMENTATION? VERY BAD TO FIND DATA ON THE INDEXES?
I want to re-create all my indexes and have them counter2id, companyid, counter2id. ( counter1id is a counter for each company, and counter2id is a global counter like identity ) GOOD OR BAD IDEA?
Since I have hundreds of indexes what is the correct procedure? I was thinking on scripting the database, then dropping all indexes, then recreate clustered indexes, then recreate other indexes. Do I have to take care of anything else foreing relationships, etc.
I have generated a database for my website, I intend on using software that will convert the database into static web pages.
Big problem I have I am not a programmer, but I know a tiny bit about tags etc. for search engines.
The meta tag description is what I want to create using a field in this database.
The software I am about to use has a sql builder is there anyway it could be done be highlighting the relavent field and using sql language.
PLEASE someone Help
This problem has been driving me around the twist.
Hi, guys,
I'm using MSSQL 2005, so I would like to know how to generate Serial no into database?
for example:
- serial no will be increase for each time confirmation has been done, The Serial no will insert into a new field as below:
00000001
00000002
00000003
and next...
thanks and regards
Hi,
I want to get information about all the indexes in a database.
Can any one suggest me a query for this.
Thanks.
There are too many indexes built on DB. As per the naming convention it seems the indexes are built as per the suggestions provided from execution plan. I presume most of the indexes are used only once in a month for the reports but are hampering the performance of daily running queries. These are also occupying a lot of space.
To confirm on this I have used the below query to know & identify the unused indexes. I have recorded the counters before and after the huge operations and I observed NO CHANGE in any of the values.
What the below values exactly indicate and when do they change? Is it good to delete the indexes having low USER_SEEKS, USER_SCANS, USER_LOOKUPS?
Query:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
[Code] .....
I've registered with GoDaddy for my site. I've used Visual Web Dev 2005 with SQL Server Mgmt Express 2005 to create my database etc. Now with GoDaddy, I need to "recreate" the database on their server again, but I don't want to go throug the whole process again. I just want to use their query editor to generate the database online.GoDaddy mentions a "personal-sql" file that SQL Server 2000 generates, but I can't find anything like that with what I have. Thanks.
View 1 Replies View RelatedHi All,
I am using scptxfr.exe to get the structure of a dababase. Problem is that its creating GRANT statements which I don't want. Also its selecting sysusers object. I have checked the parameters. My parameters look something like this:
dos promptmssql7upgradescptxfr.exe /S(servername) -d (databasename) /P sa /f (filename) -r
How can I run this utility without creating grant statements?
Any thoughts will help!
Thank you in advance!
Good day,
I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.
Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?
Please someone help, this is driving me insane.
Thanks
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
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!
Hello there,
Im alittle stuck
what im trying to find out is:
MS SQL 2000
1. How to Generate Reports from selected Database information
2. then email that report weekly.
Ive heard about Visual Studio .NET 2003: Business Intelligence Projects
but is there away to create the reports directly in Enterprise manager?
any pointers would be great
Hi,
I have a MSSQL database running in a Microsoft cluster. I was working on troubleshooting for another SAP problem when they suggested I change the setting using hte SQL Server Client Network Utility to 'named pipes' from 'tcp/ip'. Every since, despite changing it back I'm unable to start the database in the cluster.
I'm kind of a newbie at this so I'd really appreciate any help in getting this database up and running.
Here is the error message from the event log:
sqsrvres] checkODBCConnectError: sqlstate = HY000; native error = 0; message = [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
In SQL 2000, when you generated an SQL script for a database, it was logical and the tables in the script were in alphabetical order. In SQL 2005 they are all mixed up. Am I missing something?
Thanks
Peter
Please help, i am really really struggling for a logic to handle 100's of reports we have via button click from asp.net webform.
in the button click i am constructing the url :
**************************************************************************************************************
http://localhost/reportserver?/MyReports/StatusReport&UserID=1&ContractID=1&subcode=null
*************************************************************************************************************
I have a table would like to maintain the parameters required for the chosen report:
when the user chooses from list box on the webform for StatusReport, immedeately it fetches the parameters related to Statusreport and gets everything which is stored with a space in between for each parameter, for this report i have 3 parameters:
UserID ContractID subcode
now how can i construct the string based on the above parameters , i am using vb.net as code behind for my webform(asp.net)
please any ideas will help me achieve the logic. please help thank you all very much.
Scenario:
We run a multiple database environment, with two of the databases receiving most of the user activity. (both write and read). These databases are roughly 25gb each and receive roughly the same amount of activity. Currently both of the .mdf files sit on the same drive shelf. Their log files are located on a separate drive shelf.
Debate: We have an extra fiber channel shelf available for us to use. We are not having too many problems related to performance, but we are always seeking for different ways to increase application/server performance. The debate centers on what to do with the extra shelf. There are two different suggestions on how best to use the shelf. They are:
1)Separate the .mdf files for two most utilized databases. This would separate the databases and the I/O associated with each across two different shelves
2)Break off the indexes for all 5 databases on to the extra shelf. This would leave all the .mdf files on the same shelf, but it would move the I/O associated with the indexes to a different shelf.
Can anyone provide the pros and cons of either suggestion?
I would like to see arguements for either side.
Dave
When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.
so reinitialized with new snapshot but all the indexes are gone from the subscriber database.
How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot
We have transaction replication set up on one of our servers whose replicated DB is used for reporting purpose:
Now, the replicated database "D" of size 350 GB has mainly grown huge as compared to Published DB (200 GB) because of the index sizes at subscribed DB. Therefore:
I have found that Database D has one table of data size 15 GB, with rows 8349533 and index size 28 GB the biggest in the database. And adding have total of 109 Non clustered indexes:
On using SP_blitzindex i have found approx 50 NC indexes unused with below usage:
Reads: 0 Writes:273,243 and total size of indexes being 18 GB
Last User Seek
Last User Scan
Last User Lookup all '0'Â
0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates;Â
except for few 3 or 4 where updates are more than 4000.
Is this sufficient enough to delete all the above 49 unused NC indexes? And Can i create Missing index on Subscriber database?
Hi all,
I have to change the date format for one database in a group of databases in my sql server 2000,Can you please tell me how to change the date format.
thanks in advance
Hello Everyone
How do you generate a script for all the current sql server logins and generate a script for database users for each database.? You can script operaoers, tables, databases, and a lot of other objects by using the “All Tasks” shortcut menu option, but I haven’t figured out to script logins and database users. Any help would be greatly appreciated.
I’m using sql server 2000 and Enterprise Manager.
Thanks
GEM
I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating the script, but it generates insert queries for all data. How can get the script to have only top 10 record insert statements? These top 10 should be ordered by the primary key.
View 4 Replies View RelatedHi,
How can I copy a database table with all its data, indexes and constraints to a new table in the same database in sql server 2005
Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.
View 4 Replies View RelatedI written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR
[Code] .....
how do I move my database schema from 1 machine to another? Its a MSDE 2000 DB and Im using visual studio 2003.
In VS when I goto the server explorer and right click on a DB the "generate create script" options is greyed out.
What am I doing wrong?
If you cant help me with the above question could you please recommend another way for me to get my DB schema with all its constraints from one of my work stations to another. I dont want to manually recreate it.
Thankyou