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.
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
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 RelatedI 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
I am new to programming trying hard to learn. I made a code for Views in mssql for the 2 tables below:I am wondering if I could make it 3, 4 or more tables at the same time. How can incorporate in the following codes I made.
Create View vwEmployeeInfoByGender
as
Select FirstName, MiddleName, FamilyName, Gender, BirthDate,
MaritalStatus
from tblEmployeeInfo
join tblGender
on tblEmployeeInfo.GenderID
= tblGender.GenderID
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 RelatedI 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
Hi
In SQL Server 2000 & 2005
How to Display list of Tables & S.Ps & Views in a Database?
Please advise
Thanks
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 RelatedHi,
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...
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 Relatedhow to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
View 1 Replies View RelatedI'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?
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
I have SQL 2008 R2 version. The server has enough disk space where the SQL is running. But the log file is not refreshed. in other words a new sql server log file does not create a new file if the old one is full.
View 4 Replies View RelatedHi
I have a package that takes data from 3 different sources, unions them all together and then performs a lookup against a tabel on a different server. The 3 sources of data creates a dataset with quite a large volume of rows (approx 100,000) and the look up takes an age to complete.
Is there anyway of adding an index of sorts to the dataset created by the union to speed the process up? I guess i could output the union all to a temp table and look up against this but wondered if there was another way
thanks
scott
Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
I am looking to create a server wide trigger which will fire whenever a database gets created, I would like to know if there are any gotchas or anything that I need to look out for/test specifically.
The idea is to automatically grant some permissions and take some other actions whenever a database gets created.
I use below trigger to email me when a database is created or dropped.
CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd VARCHAR(MAX)
DECLARE @tsql VARCHAR(MAX)
SET @tsql = EVENTDATA().value
[Code] ...
Is there a way we can get notification when a table is created or altered or dropped ?
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?
I've installed Reporting Services 2008R2 STD edition on my local system. I was trying to configure the Report Server, I am able to connect to the Report Server but the ReportDB and ReportTempDB are getting created in Database Engine instead of Report Server, when I connect to Report Server from SSMA this is what I see (Attached) Screen 1.
The screen2 is of Reporting Service Config Manager, When I connect to the report server using the URL what I am seeing is blank last screen shot attached.
If I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.
But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.
This can be reproduces with the following code:
DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)
[Code] ....
/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2
Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).
I have an issue with fragmentation SQl server 2012 .I monitored indexes using sys.dm_db_index_physical_stats. fragmented % is still remain after re-organized the index.
View 9 Replies View RelatedLets say I have a table called MyData.
MyData has two columns, ID (int), and Value (varchar(x) )
Lets say the data in the table looks like this:
ID Value
- -----------
1 A
1 B
1 C
2 A
2 D
3 E
3 F
3 A
3 G
now, is there any way to construct a select statement from this data that will return a dataset that looks like this?
ID ALL_VALUES
- --------------------
1 A,B,C
2 A,D
3 A,E,F,G
In other words, is there a way to combine all the Value field values into one field for each ID?
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,
Can someone help me with Query to list all indexes (name, table, field etc.) on a particular user/dbo database in sqlserver.
Thanks
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?
How can I find out who created a table?
View 6 Replies View RelatedHi
I have created a new login in SQL Server 2000.
I have hooked up to more than one database which creating the login.
In syslogins we can see only the default database associated with that login.
I want the list of all the databases for that specific login..
How could I get them?
-------------------------------------------------------------------------------
My main task is to create a login under a specific database.
For which I'm validating whether that login already exists or not I'm checking it in the syslogins table. I need to specifically check it is in my databse or not....for that what should I do...
Thanks in advance
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?
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
How can I list indexes with a (SQL) query ?
Thanks
We have are SQL 2014 Standard edition, I have a situation where-in I plan to partition a table now since table partition is not supported in standard version I thought about Partitioned views however now I am stuck where I cant make the view writable because of the identity column in the base table.
Do I have any other option in this case?