How To List Tables,indexes,views Etc...

Jun 16, 2003

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

View 3 Replies


ADVERTISEMENT

DB Engine :: How To Find List Of Indexes On Tables On Which Views Has Been Created

Aug 28, 2015

The 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.

View 4 Replies View Related

Should I Create Indexes On Views Or Tables

May 22, 2007

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

View 4 Replies View Related

How Can I Use The GetSchema To Get A List Of Only User Tables And Views.

Apr 26, 2006

How can I use the GetSchema method new in 2.0 to get a list of only user tables and views.

View 6 Replies View Related

Print List Of Queries, Tables, Views And Sp

Oct 29, 2007

I just started a new job and 1st time on sql server, how can i print list of queries, tables, views, stored procedures and functions?

View 6 Replies View Related

How To Display List Of Tables && SP && Views In A Database?

Aug 16, 2007



Hi
In SQL Server 2000 & 2005

How to Display list of Tables & S.Ps & Views in a Database?

Please advise

Thanks

View 4 Replies View Related

How Can I Get The List Of Tables And Views Together In The Database By The Dbconnection.getSchema()

May 3, 2006

I have tried SqlConnection.getSchema("Tables"). it returns me a list of all tables while getSchema("Views") returns me the views in the form of DataTable. But can we have a way to get them together in the same DataTable as a return result? because I want to read the rows by  DataTable.CreateDataReader() later.

View 2 Replies View Related

How To Find Out The List Of All Tables And Views Owned By A Particular User In Any Database?

Oct 15, 2001

How to find out the list of all tables and views owned by a particular user in any database by querying the system tables?Thanks.

View 2 Replies View Related

How Do I Grab A Simple List Of SQL Server Views, Tables Or Sprocs?

Jul 20, 2005

I need just the names of tables, views and sprocs within a SQL Serverdatabase. What's the easiest way to do this?

View 3 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

EAV Performance And Clustered Indexes On Views

Jan 25, 2008

OK so I have this EAV system on a server that is old enough for kindergarten. Insanely enough, this company that makes more money than any of your gods can not buy me a new box.

Before you say "redesign", I need funding allocated for that. See my first statement.

Anywho, I have this page that touches the dreaded Value table and does a clustered index seek on it. Can't search faster than that, right? Well I am getting some funding for "performance tuning". I am wondering if maybe incorporating some clustered index views involving the value table and producing a smaller clustered index for it to seek may alleviate some of this. Any thoughts?

View 10 Replies View Related

List Of Keys, Indexes

Oct 29, 2005

Hello,
 
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,

View 1 Replies View Related

Query To List All Indexes In Db

Oct 17, 2007

Can someone help me with Query to list all indexes (name, table, field etc.) on a particular user/dbo database in sqlserver.

Thanks

View 3 Replies View Related

SQL Server 2014 :: Indexes And Views - Manage Date Filtering Within A Query?

May 21, 2014

I 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?

View 9 Replies View Related

Getting List Of Indexes Accross Servers

Mar 30, 2000

Is 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

View 2 Replies View Related

Query To List Indexes From A Table ?

Sep 1, 2006

How can I list indexes with a (SQL) query ?
Thanks

View 1 Replies View Related

Comma Delimited List Of Views

Aug 13, 2007

Is it possible to get a comma delimited list of the views in a DB?

View 7 Replies View Related

How Can I Get A Comma Delimited List Of The Views In My Db

Mar 18, 2008

How can I get a comma delimited list of the views in my db?
("view1","view2","view3",etc...)

View 3 Replies View Related

Get The List Of Catalog Views In SQL Server 2005

Oct 10, 2006

How to get the list of catalog views sys.* (sys.objects, sys.columns ....) in sql server 2005

select * from sys.objects where objectproperty(object_id, 'IsSystemTable') = 1

did not return the list as I've expected

thanks

View 4 Replies View Related

SQL Server 2008 :: Display List Of Indexes - Add Database Name In Results

Apr 6, 2015

I 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] .....

View 2 Replies View Related

TABLES AND INDEXES

Jun 7, 2001

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 Related

Indexes On Tables

Oct 9, 2001

Hello 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,

View 3 Replies View Related

Indexes On Tables

Mar 17, 2006

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

View 7 Replies View Related

What Are The Biggest Tables And Indexes?

Apr 17, 2008

Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?

thanks

View 1 Replies View Related

Indexes On Related Tables...

Jun 17, 2007

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

View 5 Replies View Related

What Are The Biggest Tables And Indexes?

Apr 17, 2008

Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?

thanks

View 4 Replies View Related

Views Vs Tables

Oct 16, 2007

I am an SQL beginner / intermediate. 
My question is assuming everything is equal is a view of a 4 table join slower than the SQL query of the 4 table join when running reports?
 

View 3 Replies View Related

Views Vs Tables

Jun 29, 2001

My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly

View 3 Replies View Related

Views Vs. Tables

Aug 24, 2000

Are views used for securiry reasons, such as restricting access to certain columns/rows or is there performance advantages to using views. There is disagreement in my dept.

I created the following view PAYACTIVE:

Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'

The PAYACTIVE view contains 260 rows. The PAYMENTS table contains over 2 million rows. I executed the following commands:

Select * FROM PAYACTIVE

Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'

They seem to take the exact same amount of time. In other words, selecting from the view seemed to not help performance. Am I conducting a valid test?

View 3 Replies View Related

Views Vs Tables

Jun 29, 2001

My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly>

Emily

View 1 Replies View Related

256 Tables In Views

Aug 30, 2005

I have a SQL script that I attempted to write into a view. If I run the script in SQL Query Analyzer it works perfectly. When I attempt to run execute as a view I receive the following error message: "[MS][ODBC SQL Server Driver][SQL Server] The query and the views or function in it exceeded the limit of 256 tables."

My objective is to use the recordset from the view to create a crystal report but have been unable to replicate the "query" effect directly in crystal. (unfortunately I am also a new crystal user)

All my searches have been fruitless. I remember seeing something about SQL Server 2000 supporting 1000 tables and older versions supporting 256 in an Access forum but have not been able to find that thread a second time.

Any provided solution would be greatly appreciated. (I am getting tired of hearing the "gears" in my head grinding. Is smoke from the ears normal?)

SELECT SubID, SubMenuDesc, ItemID, ItemDesc, 0 AS ModID, 'n/a' AS ModDesc, round(ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm
WHERE (ItemPrice <> 0)

UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, round(vwModifiersNorm.ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice = 0) AND (vwModifiersNorm.ItemPrice <> 0)

UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, ROUND((vwSubMenuNorm.ItemPrice + vwModifiersNorm.ItemPrice)/100, 2) AS ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice <> 0) AND (vwModifiersNorm.ItemPrice <> 0)

View 13 Replies View Related

Changing Indexes In Replicated Tables

Dec 19, 2001

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

View 2 Replies View Related

How Much Space In A Db Is Available For Allocation To Tables And Indexes?

Nov 19, 2007

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

View 1 Replies View Related







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