Listing All Indexes

Jul 20, 2005

Still using SQL7.

I am wondering how come there is not an Information_Schema view that
lists indexes? Information_Schema is supposed to be the safest way to
obtain information on metadata, but it appears that the only way to
get a list of indexes is with a system stored proc.

View 1 Replies


ADVERTISEMENT

Listing Indexes

May 31, 2001

Is there a way to list all table index in a user database? Not the system index.

View 2 Replies View Related

Listing All Indexes Within A Database

May 23, 2007

Working with sql server 2005. Is there a way through Management Studio to show all indexes within a db?

thanks.

View 2 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

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?

View 5 Replies View Related

Job Listing

Mar 9, 2006

Hi fellas.

I need you to answer a question:
What should I do and which SQL Server tool do I have to use in order to find a job listing on the database ?

Thanks in advance,
Abrahão.

View 2 Replies View Related

ASP.Net Unleashed Listing 12.16 Example

Dec 2, 2004

I am trying to get the following code to work but I keep getting an error.

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__title__060DEAE8'. The conflict occurred in database 'pubs', table 'titleauthor', column 'title_id'.


Has anyone else experienced a problem with this example? Let me know what is wrong
with it.

Thanks,
Ralph


<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

Sub Page_Load
Dim dstPubs As DataSet
Dim conPubs As SqlConnection
Dim dadTitles As SqlDataAdapter
Dim dtblTitles As DataTable
Dim drowTitle As DataRow
Dim objCommandBuilder As New SqlCommandBuilder

' Grab Titles Table
dstPubs = New DataSet()
conPubs = New SqlConnection( "Server='(local)';Database=Pubs;trusted_connection=true" )
dadTitles = New SqlDataAdapter( "Select * from Titles", conPubs )
dadTitles.Fill( dstPubs, "Titles" )
dtblTitles = dstPubs.Tables( "Titles" )

' Display Original Titles Table
dgrdOriginalTitles.DataSource = dstPubs
dgrdOriginalTitles.DataBind()

' Add a Row
drowTitle = dtblTitles.NewRow()
drowTitle( "Title_id" ) = "xxxx"
drowTitle( "Title" ) = "ASP.NET Unleashed"
drowTitle( "Price" ) = 1200.00
drowTitle( "Type" ) = "Mystery"
drowTitle( "PubDate" ) = #12/25/1966#
dtblTitles.Rows.Add( drowTitle )

' Delete the First Row
dtblTitles.Rows( 0 ).Delete()

' Double the price of the Second Row
drowTitle = dtblTitles.Rows( 2 )
drowTitle( "Price" ) *= 2

' Generate the SQL Commands
objCommandBuilder = New SqlCommandBuilder( dadTitles )

' Update Titles Table
dadTitles.Update( dstPubs, "Titles" )

' Display New Titles Table
dgrdNewTitles.DataSource = dstPubs
dgrdNewTitles.DataBind()
End Sub

</script>
<html>
<head>
<title>UpdateDataSet</title>
</head>
<body>
<h2>Original Titles Table
</h2>
<asp:DataGrid id="dgrdOriginalTitles" Runat="Server"></asp:DataGrid>
<h2>New Titles Table
</h2>
<asp:DataGrid id="dgrdNewTitles" Runat="Server"></asp:DataGrid>
</body>
</html>

View 2 Replies View Related

Index Listing

Aug 28, 2003

I want something like:

select TableName, IndexName
from SomeSystemTables
where TableName like 'Src%'
and IndexName not like '_WA%'

I plan on using this in a cursor in order to delete all indexes. Then I rebuild all tables from an old VMS.Ingres database and create new indexes.

I've looked over previous posts on listing indexes, but don't get the method for identifying both the tablename and the indexname in the same query. DB is SQL2000.

Thanks,

Al

View 2 Replies View Related

Index Listing

Mar 21, 2001

Does anyone know of a system stored procedure that I can run to print a list of indexes for a specific datbase. I know I can do it for a specified table, but I would like it for all tables. Thanks!

View 4 Replies View Related

Listing Db Values

Mar 12, 2004

Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:

Oracle:
select * from all_tables
select * from user_tables

SQLserver:
select * from sysobjects where type'='U'

So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.

Tnx.

View 2 Replies View Related

Listing Zero Count

Jan 22, 2007

I have a query that lists the names of all employees and the number of training modules they have sat. The query is below:

SELECT distinct pps_principals.name AS principals_name,
COUNT(*) AS coursecount
FROM (PPS_SCOS JOIN PPS_TRANSCRIPTS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID)
JOIN PPS_PRINCIPALS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
and pps_scos.name like 'MT%'
and pps_principals.login like '%testlogin%'
and pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
GROUP BY pps_principals.name
ORDER BY coursecount desc

The cont goes all the way down to those who have sat 1 module.

I now however need to be able to report all those names of individuals who have sat 0 courses.

Any help appreciated.

View 4 Replies View Related

Listing Data

Mar 30, 2008

sample Table and records.

id uname punchdate punchtime
1 Â A Â Â Â Â 1/1/2007Â Â 7:00am
1 Â A Â Â Â Â 1/2/2007Â Â 8:00am
1 Â A Â Â Â Â 1/4/2007Â Â 7:30am
1 Â A Â Â Â Â 1/6/2007Â Â 7:40am

let say i want to get a result which punchdate is from 1/1/2007 to 1/8/2007, how can i get a result like this one.?

1 Â Â AÂ Â 1/1/2007 Â Â 7:00am
1 Â Â AÂ Â 1/2/2007 Â Â 8:00am
1 Â Â AÂ Â 1/3/2007 Â Â <null>
1 Â Â AÂ Â 1/4/2007 Â Â 7:30am
1 Â Â A Â Â 1/5/2007 Â Â <null>
1 Â Â A Â Â 1/6/2007 Â Â 7:40am
1 Â Â A Â Â 1/7/2007Â Â <null>
1 Â Â A Â Â 1/8/2007 Â Â <null>

listing all data even if theres no punchdate and time in the table.

View 2 Replies View Related

Users Not Listing In Database.

Jan 25, 2001

Hello Experts,

I copied a database from my production server to the development server and now i dont see the users in the database from the enterprise manager,database and users folder. But, When i run a query to against the sysuser table from a query analyser I can see those users here. Why cant I see in from the Enterprise manager. Any advice please..


Appreciated any comments on this.
Thanks,

Vilke.

View 2 Replies View Related

Listing All Months Regardless Of Values

Jun 15, 2005

I have written the following query which returns the number of orders received grouped by the year and month:
SELECT DATEPART(yyyy, order_placeddate) AS year, DATEPART(mm, order_placeddate) AS month, count(order_id) AS orders
FROM orders
GROUP BY DATEPART(yyyy, order_placeddate), DATEPART(mm, order_placeddate)
ORDER BY year, month
year month orders
---- ----- ------
2004 6 17
2004 7 37
2004 8 30
2004 9 42
2004 10 34
2004 11 46
2005 1 25
2005 2 7
2005 4 1
The obvious problem with the above is that it misses out the months that have no orders, i.e. December, March, May, etc.

Is there a way I can amend my query so that it shows all months regardless of whether any orders were placed?

I have thought about trying to LEFT OUTER JOIN the above to a table that has rows with values of 1 – 12, but I’m not convinced this is the answer... and I don’t really know how to do it!

Do let me know if any of the above is unclear – what I’m after is the following:
year month orders
---- ----- ------
2004 6 17
2004 7 37
2004 8 30
2004 9 42
2004 10 34
2004 11 46
2004 12 0
2005 1 25
2005 2 7
2005 3 0
2005 4 1
2005 5 0
Many thanks

View 1 Replies View Related

Listing All Tablenames In A Database?

Jun 24, 2004

Is it possible to list all the tables in a database? I need to list the tablenames, is that possible with a .mdb database?

Only by using SQL, I want no frontend programming.

And is it possible to list all fieldnames of a known table.

All help appreciated
Lethean

View 9 Replies View Related

Listing All Tables And Their Columns?

Jun 14, 2006

Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.

I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.

Any help would be greatly appreciated. Thanks!

View 2 Replies View Related

Listing Orders Without A Date

Apr 25, 2012

I'm suppose to List all orders where there is no shippedDate (just give their orderNumbers).

I have this:
select orderNumber from orders where shippedDate= null;

I'm not sure what to put in place of the "= null" as that is clearly wrong

(Note: If I use "select orderNumber from orders where shippedDate;" it prints out all the orders, but I only want the orders where there are no ShippedDate...

View 3 Replies View Related

Server Listing For TCP Connection

Oct 25, 2005

I'm having trouble with an ODBC User that can't connect to 2003Windows Server with SQL2000.The ODBC fails when he tries to contact with TCP client connection.When I view the Network Connection it is displaying TCP is checked,but when I view the error log is doesn't display that it is listeningfor TCP.Is there any way to prove that my TCP connections are workingcorrectly or a way to make sure that my SQL server is listening forTCP?Thanks for reading my note and any help with this issue is greatlyappreciated.

View 2 Replies View Related

SSIS Package Listing By Name

Sep 21, 2006

I have 100s of packages under one project and finding it difficult to locate a package since it is not in any order. Is there a way to list packages by its name - alphabetically sort ?

Thanks

View 3 Replies View Related

Listing Foreign Keys

Dec 12, 2007

If I run the following code (quote many times on the internet), which should show all foreign keys in a database, but it only returns a partial list:


select CCU.table_name src_table, CCU.constraint_name src_constraint, CCU.column_name src_col, KCU.table_name target_table, KCU.column_name target_col, RC.UPDATE_RULE, RC.DELETE_RULE

from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

where CCU.constraint_name = RC.constraint_name and RC.UNIQUE_CONSTRAINT_NAME = KCU.CONSTRAINT_NAME

order by CCU.table_name, CCU.constraint_name


If I run the following, removing the KEY_COLUMN_USAGE link, it returns the full list:


select CCU.table_name src_table, CCU.constraint_name src_constraint, CCU.column_name src_col, RC.UNIQUE_CONSTRAINT_NAME, RC.CONSTRAINT_NAME, RC.UPDATE_RULE, RC.DELETE_RULE

from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC

where CCU.constraint_name = RC.constraint_name

order by CCU.table_name, CCU.constraint_name


I can't figure out what the problem is, the ones that are being left out of the first query ad the ones where the unique_constraint is set to the column name, not the constraint's name. For example, the first one appears in both query results, the second one only appears in the secod queries results:

src_table src_constraint src_col UNIQUE_CONSTRAINT_NAME CONSTRAINT_NAME UPDATE_RULE DELETE_RULE
awards awards_FK00 award_type aaaaaaward_types_PK awards_FK00 NO ACTION NO ACTION
awards awards_FK01 personid personid awards_FK01 NO ACTION CASCADE

Any help would be great, thank!

Michael

View 6 Replies View Related

Network Instance Listing

Feb 6, 2008

Hi All,

I have installed SQL Server Express on several machines on my network using same command line, configuring remote connections to be allowed. I have also added SQL Server and SQL Browser to the exceptions list in Windows Firewall.

All the installed intances in the network are listed when using SMO enumeration to browse through the servers.

But, in one of the machines, only the local SQL Express Instance is listed. The same is the case when trying from SQL Server Management Studio. But when I manually type the remote instance name it successfully connects.

I also found that adding SQL Management Studio to the list of exceptions in the Firewall solves the problem. But I don't want to install Management Studio in my client machines.


Any alternate solution will be helpful

View 1 Replies View Related

SSIS Error Listing

Jun 9, 2005

Is there a list of all the SSIS Error codes and what they

View 15 Replies View Related

Listing Tables From A Filegroup Using Sql Statement

Jul 30, 2001

Hi everybody,
Is there anyway we can find the list of tables in each filegroups by writing a sql query..

Thanks
muthu

View 1 Replies View Related

Display Only The Listing With The Lowest Price (was Need A Little Help Here.....)

Mar 13, 2006

I have a little problem that I just haven't been able to solve. I don't think it is very difficult but I can't seem to make it work. Here's the scenario:

I have a database with the following values:
Model Make Price
DA1100 GTN$88.00
DA1100 GTN $100.00
DA1000 GBN$110.00
DA668 GTN$100.00
DA880 GTN$200.00

In this case DA1100 is listed twice with 2 different prices. I only want to display the one with the lowest price. So the result I want is:
Model Make Price
DA1100 GTN$88.00
DA1000 GBN$110.00
DA668 GTN$100.00
DA880 GTN$200.00

View 1 Replies View Related

Listing Procdure/trigger SQL Code

Aug 2, 2006

I would like to create a script that lists out all of the SQL code for the procedures and triggers I have created in a database.

What I want to do is save the code for each procedure/trigger as a *.SQL file without having to open up each one in Enterprise Manager and save them individually.

Listing the names is easy by looking in sysobjects, but is the SQL code stored in a system table anywhere?

Does anybody have any alternative approach to this problem?

Thanks in advance,

Mark

View 1 Replies View Related

Listing Words With No Of Occurances In Sql Server

Dec 18, 2006

Hi,

Is it possible to get words from text columns in a sql server database in the order of their occurances in that column with full text search or by any other method.


Thanks in advance.

View 14 Replies View Related

Tool For Listing Stored Procedure

May 14, 2004

Does anyone know a program which I can use to make a list of stored procedures I have in a particular database?

I looking for something like that gives me an output as under here :)


USP_GetUSerInfo

Create procedure USP_GetUSerInfo

@Domain VARCHAR(50)

Select *
FROM V_GetUserInfo

GO
----------------------
and so one for every stored procedure in my DB :)

Greets,
Geoff

View 2 Replies View Related

Avoid Listing Hundreds Of Columns

Jun 3, 2014

i have a table with a few hundred columns. Each SELECT statement, I list each of the columns, this is taking lots and lots of space and it is difficult to review the code due to its length...Below, I have to list out every column when I only want to use case logic on 1 column. In the next step I will have to list out every single column again

SELECT
ACCT1_NO
,ACCT1_DT
,ACCT1_RISK
,ACCT1_RISK_WEIGHT
,ACCT1_CUSTOMER_NAME
,ACCT1_CUSTOMER_ADDRESS
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
,ACCT1_CUSTOMER_CITY
,ACCT1_CUSTOMER_ZIP
--,THIS CONTINUES DOWN FOR ANOTHER 150 OR SO ACCTS.
INTO #A1
FROM STAGE.CUSTOMER_ACCTS

Is there a way I can tell SQL to take all of the columns and then list the column where I want to do my case statement. Something like the code below

(which will fail as ACCT1_CUSTOMER_ST will be listed twice.
SELECT *
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
INTO #A1
FROM STAGE.CUSTOMER_ACCTS

View 1 Replies View Related

Listing Recovery Modes On Databases With SP Or T-S

Mar 20, 2007

I need to display the recovery mode of ALL databases on a server into a table. (Just like the sp_helpdb prcedure extended with the recoverymodel of each Database).
Can anyone give me a hint on this or provide me with a little tsql code snipplet to realize this?

Thanks alot in advance

View 4 Replies View Related

Listing SQL Server Databases On A Network

Dec 7, 2005

I would like to provide users of a client program a list of databases on thenetwork that match a certain pattern, like "%frp%". I have seen a list ofdatabases in the Windows XP ODBC configuration tool. What I would like toknow is can I get that information through a query to one of the databases,or is there a control or application on the client machine that I can callto get the list?Does the thread "Databases not showing up in Enterprise Manager" refer towhat I am trying to do?Best regards,Steve Caldwell

View 5 Replies View Related

SQL ODBC Table Listing Is Not Showing

Nov 8, 2006

I have created a System DSN using the SQL Server ODBC driver and configured it to point to my server and database.

 

When I access the newly created DSN, it should show the tables listing.  However, I get the following screen:

See it here

 Why am I getting this instead of the tables listing and how do I fix it?

 

Thank you!

View 6 Replies View Related

Still Error In Service Listing Manager....

Sep 20, 2006

Hi Remus:

I am using latest ServiceListingManager v1.1.3 and still getting this error....

Msg 6522, Level 16, State 1, Procedure ssb_create_certificate_from_blob, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'ssb_create_certificate_from_blob':
System.Data.SqlClient.SqlException: A certificate with name 'CMA_sbuser' already exists or this certificate already has been added to the database.System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServiceBroker.Samples.CertificatesBlob.CertificatesBlob.ssb_create_certificate_from_blob(SqlString databaseName, SqlString certificateName, SqlString authorizationUser, SqlBinary certificateBody)

Help is greatly appreciated....

Thanks,

Pramod

View 4 Replies View Related

Sqldiag.exe Listing From SQL Server 2005

Sep 17, 2007

In SQL Server 2000, I ran the sqldiag.exe for all configuration parameters to be stored for disaster recovery references. Now, in SQL Server 2005, I want the same type of info but can't get this utility to list it. I am open for suggestions on how to get this info. Is there a way to use sqldiag.exe or should I be looking at something else?

View 1 Replies View Related

Is There A Doc Listing The Limitations Of Replication Under 2005?

Apr 5, 2007

I am looking for a doc (BOL, etc.) which enumerates things like limits on the number of columns which can be replicated, or limits on the size of the row. BOL covers this for 2000, but I can not find it in BOL 2005.



Thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved