Query To List All Indexes In Db
Oct 17, 2007Can someone help me with Query to list all indexes (name, table, field etc.) on a particular user/dbo database in sqlserver.
Thanks
ADVERTISEMENT
Query To List Indexes From A Table ?
Sep 1, 2006How can I list indexes with a (SQL) query ?
Thanks
Removal Of Selected Indexes / Script Index Create For List Of Indexes
Jul 1, 2014I'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?
List Of Keys, Indexes
Oct 29, 2005Hello,
I am new in SQL Server, I have to deal with this big database with many tables, is there any way I can get a list of all primary keys , foreign keys and indexes and on all tables?
Thanks,
Getting List Of Indexes Accross Servers
Mar 30, 2000Is there anyway that I can run a procedure that will give me all the indexes on tables that are accross multiple servers?
Thanks for any help.
Dianne
How To List Tables,indexes,views Etc...
Jun 16, 2003Hi,
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
SQL Server 2008 :: Display List Of Indexes - Add Database Name In Results
Apr 6, 2015I 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] .....
DB Engine :: How To Find List Of Indexes On Tables On Which Views Has Been Created
Aug 28, 2015The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.
query to find list of indexes on the tables on which the views has been created.
Items In List A That Don't Appear In List B (was Simple Query...I Think)
Jan 20, 2005Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
How To Query Indexes
May 24, 2001I need to know what indexes are on some particular table, their names, what columns they are on.
The databse is remote & I can't use sp_statistics, only select statement.
Any ideas anyone?
SQL Server 2012 :: Indexes Based On A Query?
Aug 18, 2014trying to create indexes on two tables:
SF_Affiliate_Customer
SF_Affiliate_Customer_Account
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE
( [afflt_intrnl_cust_ind] = 'N'
AND [afflt_empl_ind] = 'N'
AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA')
)AND
[code].....
I Need A Query To Find Indexes Names For Some Kind Of Tables
Apr 9, 2008Hi,
I need a query to get the index names of particular tables. for eg.. i have some tables like emp_data,emp_job....etc..Now i want to find all indexe names for those tablenames that starts with emp........ Plz help me...
SQL Server 2014 :: Indexes And Views - Manage Date Filtering Within A Query?
May 21, 2014I have a dynamic SQL query that uses various indexes and views.
When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:
@date is an integer.
begindate is datetime format.
begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))
The above code slows my query performance by at least 400%!
Would it be faster to add a computed column to my table, using the suggested method in the link below?
[URL] ....
Then i could add an indexed view to improve performance, or can this be done another way?
Query A List Of Databases
Oct 23, 2006anyone know how to query a list of databases?
current the sp_helpdb seems cant help me, because it query all the details, i want to do it like select * from database where database id=2
SELECT IN List Query
Apr 2, 2008I have a table that has a list of skills, ie "HP One", "HP Two"
I need to pass the these skills from my applications search page to a stored procedure, hence I have a snippet of the SP below.
DECLARE @SkillSet NVarChar (200)
SET @SkillSet = '-HP One-,-HP Two-'
SET @SkillSet = Replace(@SkillSet, '-', '''')
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (@SkillSet)
The following sp does not return any results, but when i set the last line to ..
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN ('HP One', 'HP Two')
It returns a set of results. Also when I do a select on the variable @SkillSet, ie SELECT @SkillSet, it displays 'HP One', 'HP Two'
Can enybody help me here, i Know i'm doing something wrong, but I cant think of what it is.
Thanks
SQL Query To Check Against A List
Jul 23, 2005SQL gurus...I have one table of items that is the master file. We will call this Table A.I have another table that contains few, some or all items from Table A. Wewill call this Table B.I want to run a query that compares the list of items on Table B against themaster list on Table A.If the item is not present on Table B, show it in the SQL results.Best way to achieve this? I am looping and doubling results in my sql tries.Jeff--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedQuery SharePoint List
Jan 31, 2008
Does anyone have any way to run a query against Sharepoint to return the content of a list through transact SQL?. Perferably without having to go throught the "back door" and access the Sharepoint database directly?
Thanks
SQL Statement - Query A List Of Values
Oct 31, 2006Hello I have a newbie question. If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen
View 4 Replies View RelatedQuery List Of Datetimes For Same Date
Jul 7, 2007Hi:I have created a table that has a column of smalldatetimes. For any given day, there may be different records with the same date but with different times. I've created the query SELECT Event FROM Events where Start=@Start and I input start as a selected date from the calendar control. Because of the different times, I do not get any matches. Could someone help me with a query that will get all of the events on the same day? Regards,Roger
View 2 Replies View RelatedSQL Query Linked To Drop Down List
Jan 20, 2008I have a drop down list populated using sqldatasource1 it is populated with [item]
However, I need it to be poplulated with the three fields below AND, I don't want duplicates.
SELECT [Item], [Alias1], [Alias2] FROM [Authors]
Query To Get List Of Tables From Database
Sep 21, 2004Can some one please tell me whats a best way ot get a list of all the tables that are present in a particular database.
Thanks
SP Or Query To List Locks Or Deadlocks ?
Sep 18, 2006What could be the query or the stored procedure to list locks or deadlocks ?
Thanks
Query List Of All Tables In A Database
Mar 5, 2014I'm running SQL Server 2012 Express. I need to query a database and return the list of tables in the database. How to construct this query?
View 4 Replies View RelatedParameterized Query For A List Of Items
Sep 2, 2014I am doing parameterized queries from Visual Basic in Visual Studio.
I have a query where I want to do something like this.
Select * from people where city in (<list of cities>)
The problem is I want to build my <list of cities> in Visual Basic and pass it to the SQL as a parameter.
I know I can't do this:
Select * from people where city in (@ListOfCities)
Currently, I'm doing this by writing the <list of cities> out to a separate table, just so I can do the query.
Select * from people where city in (Select CityName from CityTable)
List Items From A Form Query
Feb 24, 2008Hi,
I am a newbie, this is my first post (please go easy).
Iam at the moment trying to set up a query for someone looking for a property on an estate agents website.
From a drop down menu, the user can:
select an area (where they may like to live) from a list of areas.
select an amount of bedrooms from a list of bedrooms
select a minimum price from a list of prices
select a maximum price from a list of prices.
The query I worked out for this is as follows:
$data = mysql_query("SELECT * FROM property WHERE area like '$area' and bedrooms like '$bedrooms' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());
This seems to work fine and shows all the properties that meet the criteria onto my webpage.
However, I then thought, someone may not care which area they live in and want to see all properties in all the areas, so I decided to add the option 'All areas' to my 'areas' list, I then did the same for the other lists, eg 'all bedrooms' option to my bedrooms list and so on.
I am now trying to write a query that incorporates where the 'all..' option is selected and have become very stuck!
Can someone set me off in the right direction for this.
I hope that makes sense?!?!
Transact SQL :: Query To Get DB List And Size
Nov 19, 2015I need to retrieve list of all databases and the size occupied by them in sql server. As of now am pulling data for each database using sys.dqatabase_files which is hard for me as there are around 40-50 db on SQL Server.
View 4 Replies View RelatedHow To Query A Sharepoint List From SSIS
Jan 2, 2008I have been searching for a GOOD example of using SSIS to query MOSS 2007 list data. I just want to return the data from a list in Sharepoint and use that data in SSIS. It should be as simple as defining a data source to connect to a specific list and return the rows like a SQL query.
Can anyone provide a good example of doing this in SSIS (and by the way, I'm not looking for how to write a C# program) - I want to be able to do this directly from SSIS.
Any help would be greatly appreciated.
SharePoint Query On List - RsCompilerErrorInExpression
Aug 14, 2007I have been working on this all day. First day using SQL Reporting Services.
Software being used -
VS2005 - SQLServer Business Intelligence Studio - with SharePoint Windows Services 2.0 Web Services
1. I have created a Data Source - XML - Which is a SharePoint List
http://xxx.xxx.com/sites/OpRiskSP/OpRiskTechPM/MMUpdate07/_vti_bin/lists.asmx
Passed
2. Next I go to create a DataSet - ds
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/> <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>
Parameters
1. Name = listName, Value = MaturityModelWorkingCopy
Next I Run the query and boom the data returns
Then I click the refresh button to populate the Reprots Datasets - called ds
Passed
3. Layout
I am assuming you can drag and drop the data fields onto the designer which that does work
I drag the first dataField and I get a textbox =
=First(Fields!ows_ID.Value)
Not sure what the First stands for
4. Preview
FAILED
[rsCompilerErrorInExpression] The Value expression for the query parameter €˜listName€™ contains an error: [BC30451] Name 'MaturityModelWorkingCopy' is not declared.
Build complete -- 1 errors, 0 warnings
Any help would be grand.
<moojjoo />
How Will I Create A Delimited List In A SELECT Query?
Jun 10, 2007SELECT FriendName from Friends where RegionId = 23
I would like to create a comma delimited list of 'FriendName' column values in above query (example - Mike,John,Lisa,Emburey).
How would I do that?
MSSQL Query To List Out Unique Records
May 22, 2008hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
View 5 Replies View RelatedBuild SQL Query From Dynamic Checkbox List
Nov 26, 2003Not sure if this is the place to post this, but here goes.
I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.
Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.
Thanks.
Dynamically Inserting A List In WHERE Clause Of A Query
Jul 8, 2014What I need?: A way of dynamically inserting a list in the WHERE clause of a query. (in the form, WHERE ID = 1,2,3,6,9 etc)
Imagine an example DB with 3 columns Student ID, Name, Teacher_ID. (Lets assume Teacher_ID with value 100 means its the Headmaster)
I need to create a list with Student ID's, who are directly/indirectly under the Headmaster. Example:
Headmaster
Teacher 1 (ID 200)
Teacher 2 (ID 250)
Student 1 (ID 300)
Director
Teacher 4
Student 5
In the above example, since I only want those students/teachers under the headmaster, either directly/indirectly, my list would contain Teacher 1, Teacher 2, and Student 1. (In my case, just their ID's, so 200, 250, 300)
Director, Teacher 4 and Teacher 5 wouldnt be in the list since theyre not directly/indirectly Headmaster.
T-SQL (SS2K8) :: Query To List Rows Into Columns?
Aug 10, 2015I have requirement to list each row into column. I have tried with pivot query but unable to get it. I am using SQL Server 2008 database.
Here is the sample data:
CREATE TABLE dbo.test (
action_id numeric,
action VARCHAR(20) NOT NULL,
[Code].....