Know List Database And Table In SQL Server

Jun 9, 2006

I use this macro to list in A column of sheet the name of sql server on
my lan.
Is possible to "scan server by server" and insert into column B the
name of database and related table?
Tks.
Sub test_sql()
Dim TEST As String
Dim RIGA As String

Set sqlApp = CreateObject("SQLDMO.Application")


Set serverList = sqlApp.ListAvailableSQLServers


numServers = serverList.Count
RIGA = 2


For I = 1 To numServers


TEST = serverList(I)


Range("A" + RIGA) = TEST
RIGA = RIGA + 1
Next


Set sqlApp = Nothing


End Sub

View 2 Replies


ADVERTISEMENT

Database Table List

Sep 10, 2007

Is there a system table that holds the table definitions, names, properties, etc. for tables that I have designed? I would like to write a transact SQL query to insert a field in every table that I have defined.

View 1 Replies View Related

List Of Table In A Database

Apr 1, 2006

Please how can I get list of tables in a database? Thanks.

View 3 Replies View Related

List Of All Relations Of Database's Table.

Dec 16, 2006

Hi,How to get list of all relations of certein database's table?

View 7 Replies View Related

Getting Database Specific Table List From ADO

Aug 21, 2000

We are listing available SQL databases for selection in a VB list box, but need to be able to then elicit the tables in the database for another list. I am sure it is something simple, could someone help me out with this? Thanks!!
Laura

View 1 Replies View Related

List Of Table Name That Not Supported By SQL Database ???

Aug 30, 2006

Is anyone know about waht type of table or database name

that not supported or will have error during execution of SQL statement

in SQL or MS Access database. For my, I know only two ,

i.) cannot put table name that same as the the database name

ii.) cannot put database name like "RETURN", "POSITION",etc.

Is there any reference site that show out a list of table about the

table or database name ??? Thank You ... :)

View 5 Replies View Related

How To Get The List Of Table Names In A SQL2005 Database

Aug 31, 2005

How to get the list of table names in a SQL2005 database?

View 6 Replies View Related

List Table Names In A Database Having A Particular Column.

Aug 3, 2007

Can anybody tell me how to find out whether a particular column exists in any of the tables of a database and if it does, display the table names?


Thanks

View 3 Replies View Related

Create List Of Table Names And Size For A Database

May 5, 2004

Hi there,

I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB

E.g

Table1 1111KB
Table2 123300MB
Table3 120448KB

etc for all the tables in a particukar DB

I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.

can anyone help please!!

From

NewToSQL

View 12 Replies View Related

Storing A List In A Database Table (messaging Queue?)

Feb 14, 2007

hey all you database guru's hopefully someone can lend some insight as to a little table design problem I have.

Basically I've got a system in place to authorize users to access a website typical username password stuff. The table contains a list of users and there passwords plus the auth level and a few other tid bits that aren't really important enough to into detail about them here. What I want to do is add a messaging system to this, I think I could probably figure out a way to do this half decent if I setup a seperate table for each user to add a row to the table for every message entry than in my asp.net code have it delete everything but the last 10 entries every time a user logs on. However I would much prefer a way that I didn't have to setup a whole new table for each user just for messaging purposes, maybe store something like a list in one of the database cell's kind of like .nets generic.list or better yet generic.queue, I would also like a way if it's possible without too much work to have the table automatically delete the oldest message every time a new message is received if there's already 10 messages existing for the user.

Anyways hopefully someone has some experience in setting up a system like this, I don't really require any code samples I can code it all myself (other than the database code to automatically remove entry's, I'm not a database guy) if someone could just explain a way to accomplish what I'm trying to do, or if someone has a different more convenient way of doing this I would be up for suggestions


Thanks in advance for any help offered, I do appreciate it

View 3 Replies View Related

SQL Server 2008 :: Script To List All Database Level Permissions In A Database?

Aug 4, 2015

I found this script online..

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

but the results are this: 2 columns - User and Permission

User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT

IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?

View 7 Replies View Related

SQL Security :: Cannot Expand List Of Database Tables Using Contained Database Login With Server Management Studio

Jul 30, 2015

In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error: 

The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

Is there a way to set permissions for the contained user so that this could be done?

View 4 Replies View Related

Radiobutton List From SQL Server Table

Mar 29, 2007

Hi! There must be a very simple answer to this question, but it’s my first venture into using SQL Server data.
 
I’ve set up an ASP.NET site to provide for on-line sign-up of events.  The site has a masterpage and utilizes a wizard to create an e-mail to request sign-up for an event. 
 
I’d like to make my radiobuttonlist dynamic so that events past the signup deadline will not be displayed.  So, I’ve created a database table that includes two fields: event_description and event_date – such that the radiobuttonlist will only show those events that are at least three days beyond today’s date.
 
I’ve configured the data source and the radiobuttonlist so that I can display all the entries in the event table, but I can’t figure out how to restrict them to today + 3 events only.
 
I’m guessing my problem is in the “Add Where Clauseâ€? of the “Configure Data Sourceâ€? wizard.  I’m sure the “Columnâ€? should be set to event_date and the “Operatorâ€? should be set to “>â€?, but what should I set the “Sourceâ€? & “Parameter Propertiesâ€? to?  I’ve set a variable in my program as “Dim deadline As Date = Today.AddDays(3)â€?, but the wizard doesn’t seem to see it.
 
Can anyone steer me in the right direction?
 Thanks, Jerry

View 3 Replies View Related

Sql Server Database Drop Down List

Jun 27, 2006

I was wondering if anyone may be familiar with a way to create a dropdown list that included a list of SQLServers.  Basically I want to create a little application that lets a user point to an instance of sql server, similar to enterprise manager.  I wanted to use C# and figured there may be some namespaces I could use that provide this functionality but was not sure.
Thanks,

View 2 Replies View Related

T-SQL: Import Excel List Into SQL Server Table

Jan 8, 2008

Hi everyone!

I asked a similar question like this yesterday but i didnt work the way I wanted it to work. So I will ask in a diffrent way:

I have an Excel list which i want to import into a table in my SQL Server 2005. The ServerTable has one more column (for the the primary key which is created automatically) than the ExcelList. How can i import the ExcelList in a way so that I the first column of my ServerTable is not filled by a column of my ExcelList??

That is how far I came:

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D: esting.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')


many thanks and greetings from austria

landau

View 3 Replies View Related

Database List Hardcoded Into SQL Server Management

Nov 13, 2005

Hi,

View 4 Replies View Related

How To List Permissions For Public Role For A Database In SQL Server

Oct 17, 2007

Hello,
Does anybody have the transact SQL to find the permissions granted to the public role in a Database ?

Also looking for the SQL to find the permissions granted to the user Guest in a database ?


thanks

View 5 Replies View Related

DB Design :: Getting The List Of Database Names From Remote Server?

Nov 2, 2015

In our application we are copying data between 2 sqlserver databases using linked server.  Say, sqlserver1 is source and sqlserver2 is destination, then, our application will be on sqlserver2 box and will copy data from sqlserver1 to sqlserver2.

User also need to choose from which database of sqlserver1, the data need to be copied. This data is our application data.. Nothing related to sqlserver database.

We are using the following query, to get the database names of the source sqlserver..

SELECT * FROM <linked server to dest db>.master.sys.databases

My questions is : is this query generic enough that works on all sqlserver versions?

Is the master database name of sqlserver configurable or its alwasys fixed as "master"?

View 4 Replies View Related

SQL Tools :: Cannot See A Database In List From Server Management Studio

Sep 24, 2015

I can see a database file in the Data folder which is used by the "sqlservr.exe" process (detected this using Process Explorer) but I cannot a see a database in the list in SQL Server Management Studio.I am sysadmin on the server (also tried with the "sa" account).tried running "USING" statement with no luck.Is there a system view which tells the relationgship between data files and databases?

View 2 Replies View Related

SQL Server 2008 :: Converting Adjacency List To Hierarchy Table

Feb 10, 2015

I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.

My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents.

select * from SourceTable -- produces 22461 records

--step 1: first, get row number of child records in each parent

SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num
INTO #RelationshipTmp
FROM SourceTable;

[Code] ....

View 1 Replies View Related

SQL Server 2014 :: Columns List With And Without Identity Column In A Table?

Feb 24, 2015

I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .

Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)

Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)

View 8 Replies View Related

SQL Server Admin 2014 :: List Of Users That Accessed A Table

Jul 22, 2015

I'm trying to find out what tables are being used in a Database.

I don't want the last User but the User and the Dates.

I have a script that return the last user but that is not going to work.

The following script returns the last user but not all users and the Login Name:

ITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())

[Code] .....

View 2 Replies View Related

Syntax To Extract A List Of All Table Names On Linked Server

Aug 9, 2013

I'm using the following syntax to extract a list of all the table names on a linked server:

EXEC sp_tables_ex
@table_server = MY_SERVER_NAME

It outputs a list of tables into 4 columns in the result window.Is there a way an can use this as a 'SELECT * FROM ... " command so that I can organize records, insert into, etc etc ?

View 3 Replies View Related

SQL Server 2000 - View Showing The List Of Database Tables

Dec 3, 2007

 When using Sql Server Enterprise Manager and viewing a Database / Tables section, most of the tables if not all have a create date of 11/5/2004.Except for one, DNN_Users, has a creation date of 7/10/2007What factor could have caused that create date to have changed?What factors go into the date being set on that column in the database design? Does the date get updated say if I were go go in and change a datatype in a table? 

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

SQL Server 2008 :: Does List Of Page IDs Allocated For A Table Change During Index Rebuilds

Jul 16, 2015

We noticed a deadlock 3-4 weeks ago on a table (table1) and deadlock graph was captured.

When I am analyzing the deadlock graph, page number using DBCC PAGE, I am getting the object id for a different table (table2). But deadlock graph shows the name of the object as table1.

Is it possible that subsequent defragmentation of indexes would have caused the respective page id to got re-allocated to a different table? I checked the deadlock graph lately only after 3-4 weeks.

View 1 Replies View Related

SSIS Loading DWH Staging Area When Table Names Is Selected From Table List

Aug 31, 2007

Hello,
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.

I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.

how to solve that problem?

View 5 Replies View Related

Multiple Table Query - How To Get List Of All Values For Each Table

Jun 7, 2012

I have a database that has 370 tables that match %_REF_% naming. All of these tables have a column call ds_name.

I need to get a list of all values of ds_name for each table.

View 3 Replies View Related

Select Data From Table In One Database Server While Connected To Another Database Server.

Dec 18, 2007



Hi,


Is there a way in SQL Server 2005 to use a select statement to fetch data from a table in another Server while running the query in one Server.

Like using a database Server Link in oracle...


Thanks
Pramod

View 8 Replies View Related

I Imported A SQL Table Into SQL DataBase, But I Can Not Update This Table Even With SQL Server Management Studio

Jan 8, 2008

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
When I change any data on mentioned table above, Red exclamation sign appears left of the record .
How can I correct this problem?
 Thanks.

View 1 Replies View Related

Please Help Me About List Of Table

Dec 14, 2005

i need to give list all table in my database plaese give me qurity?
thanks a lot

View 4 Replies View Related

Getting A Table List

Feb 24, 2004

Hello,


If you need to have a liste of tables starting with 'WI', under Oracle for example, with a simple select of a system view (with a filter : table_name like 'WI%') you have the result.
Under SQL Server, how to do this simply (sp_tables ?) ?

Thanks in advance

View 14 Replies View Related

How To Get Table List From A Db? Thanks!

Aug 2, 2005

Hi Group!I would like to get a list of all the table names from a database, cananybody please tell me how to do that? Many thanks!

View 4 Replies View Related







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