How To Find No. Of Databases And Tables?

Mar 26, 2008

Hi,
is there any way to find out the no. of databases and
no. of tables in a particular database using a sql query ???

If any server variable is used then please mention that???


thanx in advance

San

View 20 Replies


ADVERTISEMENT

How To Find Differences In Column Data Types Between Tables In Two Different Databases Using TSQL

Apr 21, 2008


I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name. Some tables have slight differences in terms of data types or Data type lenght.

For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.

I am using SQL Server 2005. And the Two Servers are Linked Servers

What Script can i use to accomplish this task. Thanks






View 4 Replies View Related

Linking Tables From Different Databases Or Querying From Multiple Databases

Dec 10, 2007

Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query. 
 

View 3 Replies View Related

Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?

Jul 26, 2005

I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you

View 4 Replies View Related

How To Find Out? DTS Connections To The Databases

Feb 8, 2008

I am in the process of migrating SQL Server 2000 to SQL Server 2005. Moving one user database at a time to SQL Server 2005.
Is there anyway I can list the DTS packages referenced by each databases.
All our DTS packages are stored in the database. Will the query to system or meta database will help?
Thank you,
Gish Smith

View 7 Replies View Related

Find Autogrowth Setting Of All Databases

Jul 1, 2015

give me query or script to find autogrowth setting of all databases in instance

View 5 Replies View Related

How To Find Out Which Databases A User Have Access To?

Apr 3, 2007

Hi, my first question, tried to use search first but couldnät find what I need.

Hopefully someone has asuggestion.



I work for a ISV producing HR applications.

When user starts the applications the program shows all databases that the user have access to. This is done by select all names from master..sysdatabases and then trying to "USE dbname" to see if user have access,. Users doesn't have sa rights.



This procedure takes approximately 90 secs for a server with 500 databases and that has become a problem.



Does anyone know off a faster method, any suggestions?



/Regards Anders

View 1 Replies View Related

Need To Find And Document Permissions In User Databases

Feb 11, 2005

Hi folks, i've an instance with many userdatabases. i want to use SP_HELPUSER to output all database users and roles defined to em. how come i do this through a script to view permissions in all databases. I couldn't use (USE database ) in a loop.



Howdy!

View 3 Replies View Related

SQL Server 2008 :: Script To Find A Value In All Databases?

Aug 4, 2015

Script to find a value in all databases ? for example I want to find a value contain word “ SQL” in all databases so I will get the information in which database and which table it exists.

My script is only for finding a value in one database.

View 8 Replies View Related

Query To Find Databases In Full Mode

May 4, 2007

I have a stored proc that runs through my databases doing transaction logs. Currently it uses database names but this keeps being changed as a database will be added without me knowing. Next thing the transaction log job is goosed.



I really need something along the lines of select db from sys???? where mode = 'Full'



Any help appreciated.



MPM

View 1 Replies View Related

SQL Server 2008 :: Find Unused Databases Or When Last Used In A Instance?

Mar 30, 2011

Find unused databases in a instance or when last used or accessed?

I'm on SQL SERVER 2008 R2 64bit -enterprize

I need to find when the databse is last accessed.

View 3 Replies View Related

Reporting Services :: Can't Find Any Literature On The Native Mode SSRS Databases

Jun 15, 2015

I can't find any literature on the native mode SSRS databases.

so something similar to the MSDB sys tables [URL] Is documentation non existent?

View 2 Replies View Related

Recovery :: How To Find Last Login Date / Time For User DML Activity On Databases On Instance

Jun 22, 2015

How to find last login date/time for user DML acitivity on databases on Instance?

Is there any way we can find our the last login date/time for databases?

Note: 1. We can find if the SQL Trace is running and store.This is not good solution
         2. Audit logins off/on is also not good solution.
         3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.

View 5 Replies View Related

Many Databases Vs. Many Tables

Apr 14, 1999

Hi,
I'm writing an intranet application - a web survey engine.

I'd like to create a new database for each user that creates a survey form...
versus creating new tables for each survey.

Memory and disk space is not exactly the problem... just user permissions. If I dump all the results onto one database, doesn't it mean that this user can have access to all other tables(including the results of other surveys??)

Question is, does SQL Server reserve a bunch of memory for each database that I create, thereby hogging up a lot of space if I create more than one database.

Thank you.

View 1 Replies View Related

All Databases' Name With Their Tables' Name

Apr 25, 2008

hi all,

i want to write SQL query that create table and insert in this table all databases' name and their tables' name that exist in my server.

e.g

DB name Table name
------- ----------

Northwind product
Northwind customers
Northwind Employees
Pubs authors
Pubs discounts
Pubs employee
msdb log_shipping_databases
msdb log_shipping_monitor

View 3 Replies View Related

All Tables In All Databases

Mar 11, 2002

Credit for this script really goes to ToddV (see http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13737)

The following script issues a DBREINDEX command against every table in every database on your server. It can be modified to issue other commands, but this one in particular is helpful as we are migrating about 30 databases from SQL 7 to SQL 2000 (new server) and re-indexing is recommended. Here's the script:


DECLARE @SQL NVarchar(4000)
SET @SQL = ''

SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid > 6 -- skip the 6 built-in databases. Remove to process ALL

PRINT @SQL -- Only if you want to see the code you're about to execute.
EXEC (@SQL)


Notes: There is a limit (nvarchar 4000) to how big your command can be, so too many databases will halt this.

------------------------
GENERAL-ly speaking...

View 2 Replies View Related

Join 2 Tables From 2 Different Databases

Feb 6, 2007

Hi All,
 Is it possible to join 2 tables from 2 different databases in Stored Procedure.
Thanks in advance.

View 2 Replies View Related

How Can I Join Two Tables From Two Different Databases?

Aug 3, 2007

hi,
i have one database ASPNETDB.MDF created by default when adding a user to my site, and MyData.mds - my database...i want to join the aspnet_Users table with another table created by me (in myData.mds), how can i do that? is hard if i should re-write all the data from myData into the ASPNETDB,i even writed both connectionStrings in the web.config but still with no succes...
is there any trick in the SQL statment? please help me
thank you

View 10 Replies View Related

Sending Databases/tables ?

Dec 21, 2007

Hello,
Let's say I created some tables in SQL server using the designer.  Tables -> Right click -> New Table ->... How would I send someone else the script for creating that table?  Or how would I send somebody else the tables?
Thanks.

View 3 Replies View Related

Acess Tables From Different Databases

Jan 23, 2008

I am using Vista Business with Visual Web Developer and Sql Server Express 2005. I want to be able to access the data from two tables in a query. Both tables are attached to the application. 
To refer to the two databases I have used the following
DATABASE.OWNER.TABLE (specifically TaskMgr.dbo.TaskTable)
For both databases I get the error  "Invalid object name"
What am I missing?
Thanks

View 2 Replies View Related

Sharing Tables Between .MDF Databases

Jan 2, 2006

Hi :)

I would like to know if it is possible to link two databases together
(in my case ASPNETDB, and another mdf database) so that I can run
queries on those shared tables. For example, I would like to use the
uniqueidentifiers from the ASPNETDB tables in my own tables.

Thank you!

(I do use the latest version of Visual Web Developer).

View 3 Replies View Related

Updating Of The Tables In Two Different Databases

Dec 16, 2001

I am using SQL server7.0. I am having two databases ,say database A and database B.Database A is the main database which is used in two /three projects.While database B is created by me for my work.From the database A, I am using the 4/5 tables, which i have copied in to the database B.
So, i want to update these tables in the database B as soon as any change (insert,update or delete ) occures on the tables in the database A.
That's why I am interested in doing these work.I have tried, but it doesn't work.So, I have placed these into these forum.
Is any body is having the trigger, procedure ready for doing these job. then plz,mail it to me

View 1 Replies View Related

Connecting Tables From Different Databases

Apr 23, 2007

Hey everyone. I am somewhat of a newbie to the database world.
I have been given the task of connecting two different SQL databases. Both are Microsoft SQL.

I want some of the tables of one of the databases to be linked to the tables of the other.

Is this possible?


Thanks

RoadHired

View 1 Replies View Related

Combining Two Tables From Different Databases

Jul 2, 2004

I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.

To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.

To better illustrate:

database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58

The overlapping cusno's are not the same customer.

How can I get all the cusno from the arcus file in database two to the arcus file in database one?

Is this even possible?

View 4 Replies View Related

Relating Tables From Different Databases

Nov 29, 2006

Hi,

I am trying to relate two tables but both are from different databases. I have a database 'Current' which has a table 'case' and another database 'Org' which has a table 'employee'. I want to establish a one-to-many relationship between employee and case. I created a coulmn 'employee_key' in the case table & entered values matching the pkey of the employee table. Then I wrote a query using joins to access the table records.

select Org..employee.pkey, Current..case.assignedengineer, Current..case.pkey from Current..case join Org..employee on Current..request.employee_key=Org..employee.pkey

Although the above query works fine, there is no relation between the two tables of the two databases, I wanted to know if this is the right way to achieve what i want to or is there a way in which i can actually create a relationship between tables of two different datbases. Can anyone suggest/help???
Also, I wanted to know if i can relate 1 table of SQL Server db with another table of Oracle DB. Please help ...........
Thanks

View 1 Replies View Related

Join Between Tables On Different Databases

Jan 22, 2004

Is it possible to make a join between two tables from different databases on same server? if yes, how can we do that?

And also if I want to make a join between tables on different databases on different server?how to do this. Please advise.

View 2 Replies View Related

Should I Seperate Tables Into New Databases??

May 22, 2008

My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.

View 5 Replies View Related

Synchronize 2 Tables In 2 Different Databases

Apr 24, 2007

i have 2 db's that are totally different except that they both have a table users and I want to make it that anytime the user table is updated or added to in one db - that it is copied over to the other db.

is this possible?

View 9 Replies View Related

Joining Tables In Different Databases

Jan 24, 2006

HiI'm working on an ASP project where the clients want to be able toeffectively perform SELECT queries joining tables from two differentdatabases (located on the same SQL-Server).Does this involve creating virtual tables that link to another database, oram I completely on the wrong track?Any hints as to where I might find more information (buzz-words, etc.) wouldbe most appreciated.Thanks

View 10 Replies View Related

Linking Tables From Other Databases?

Jan 18, 2007

In a database, I am creating a new db. From there, I am setting up thetables, so that I can eventually create a front end (usually access,but I may attempt to be brave and lose the shell.) Anyway, I want touse a table, read-only for a lookup. It exists in another database onour system. Is there a way for me to link it into this database that Iam working on? I would think of it like in access when you go to do anew table and you choose to link the table from somewhere else. Thatis what I want to do. Can anyone possibly step me through this?I know basics of sql server and like to learn.Thanks!

View 3 Replies View Related

Retrieve ALL Tables From ALL Databases

Apr 10, 2007

(SQL 2005)I'm looking to create a stored procedure to first "select name fromsys.databases where name like '%site'" then pass each name to thefollowing using some kind of loop "USE @name select name fromsys.tables where type = 'U'"I tried a while statement, but the master sys.databases recordsetdoesn't change..It's the loop I can't get to work.Any help is greatly appreciated!

View 3 Replies View Related

Querying Tables From Different Databases

Jul 20, 2005

Hello,I am quite new to ms-sql and I have a problem : I want to create an SQLrequest which would copy serveral records from a table in a given databaseto another table (with exactly the same structure) in another database(theses two tables and databases already exist).Could you please tell me how to do this ? I dont know how to access twodifferent databases in a single SQL request.Thank you for you help.

View 3 Replies View Related

Combining Tables From Different Databases

Jul 20, 2005

Hi.I'm currently working on a project which involves the creation of aweb page which reports selected data to customers from two back-endsystems. I use ASP script language on IIS, the server is MS SQL 2000.Now I'm struggling with combining two tables from the differentdatabases. I'm sure it's simple enough, but I'm a little short on theSQL expertise.I've got two databases, db1 and db2, and then two tables, db1.t1 anddb2.t2. I need to combine these two tables (both tables have amatching key field) to make a list of all items from db1.t1, and thosewho correspond from db2.t2.I can list all items from db1.t1, but I can't seem to get the db2.t2joined in.Can anybody help me with the syntax for this, please ? Help !Answers, hints & tips greatly appreciated.Thanks in advance !Kenneth

View 2 Replies View Related







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