Listing tables in a database

Hello Group,
how can I run a query in the QA to list the table names in a particular
database?
RichRich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
> > Hello Group,
> >
> > how can I run a query in the QA to list the table names in a particular
> > database?
> >
> > Rich|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
> > Rich,
> >
> > Use view INFORMATION_SCHEMA.TABLES
> >
> > use northwind
> > go
> >
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where TABLE_TYPE = 'BASE TABLE'
> > go
> >
> >
> > AMB
> >
> >
> > "Rich" wrote:
> >
> > > Hello Group,
> > >
> > > how can I run a query in the QA to list the table names in a particular
> > > database?
> > >
> > > Rich|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'

ListAvailableSQLServers

I have a VB6 app that uses ListAvailableSQLServers to collect the server names on a computer. On Windows XP, however, the default instance shows up as (LOCAL) while @.@.servername is the computername. When my app tries to select from (LOCAL).db.dbo.table, it gets "SQL Server does not exist or access is denied." If it selects from "computer".db.dbo.table all is well.

Any thoughts how to get around this? Aliasing does not work...

Thanks in advance.Plz first make sure the SQL service is running by checking it under the Services group in the Management console and what startup service account it's using; can u connect to that instance thru Query Analyzer? If yes then type in
select serverproperty('servername').
Check the mode of Authentication u r tryin to connect to SQL thru. Post some details about the startup-service account etc.

Howdy.|||Since the (local) server is nearly "hardwired" into the list, there isn't a good way to avoid it. The machine name should still appear in the list anyway. I usually just put in code to ignore both the IP address and the constant (local) as I iterate through the list of servers.

-PatP|||Sorry, SPE (Stupid Programmer Error); one of my ADO connection strings had the data source hardcoded (leftover from testing). Thanks both...|||Hmmm... We know those as ID10T errors. Same difference!

Glad you are working now!

-PatP

List the System names has sql Server installed

hi,
I need to display the system names which has sql Server installed. How it can be done in vb.net.
Help me plz
regards
Somu

All things System are in the Master Database, spend some time with there are almost 1000 stored procedures in it, those and the tables are Microsoft Property try not to use them. Hope this helps.

List table names in a database having a particular column.

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

Check INFORMATION_SCHEMA.COLUMNS view in your BOL.

AMB

|||Thanks!

List record counts of all tables?

Hi All,
Is there a fancy way to list all table names with record counts?
Using table: INFORMATION_SCHEMA.TABLES
Also, Is there a way to initialize/empty all data from all tables?
Thank you very muchUse TRUNCATE to clear out a table

as for space

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO

DECLARE @.tablename nvarchar(128)
, @.maxtablename nvarchar(128)
, @.cmd nvarchar(1000)
SELECT @.tablename = ''
, @.maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'

WHILE @.tablename < @.maxtablename
BEGIN
SELECT @.tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @.tablename

SET @.cmd='exec sp_spaceused['+@.tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @.cmd
END

SET NOCOUNT OFF
GO

SELECT * FROM #SpaceUsed
GO

DROP TABLE #SpaceUSed
GO|||I didn't mean that fancy! It worked nonetheless.

Thanks a million

List out duplicate names only

Hi all,
I am new this this site. May I know how do I do a search for duplicate items
in a column of a table only. eg If John appears twice and Jim once, in the
Name column of Student table, the Select statement should show me 2 times (
John ) ?
ThanksSee Itzik Ben-Gan's example
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Eric_Singapore" <Eric_Singapore.24lr9q@.mail.codecomments.com> wrote in
message news:Eric_Singapore.24lr9q@.mail.codecomments.com...
> Hi all,
> I am new this this site. May I know how do I do a search for duplicate
> items in a column of a table only. eg If John appears twice and Jim
> once, in the Name column of Student table, the Select statement should
> show me 2 times ( John ) ?
>
> Thanks
>
> --
> Eric_Singapore
> ---
> Posted via http://www.codecomments.com
> ---
>

List of values that make SQL Blow

I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion

Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.

IE (ticks, #, :, -,)

I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.

I know this is a nightmare but it is the contraint I am working under.Not sure... but I recently learnt that .NET doesn't like column names starting with a numeric character so worth bearing in mind if that is your FE.|||I am not sure such a list exists, as technically SQL Server will accept anything between brackets, so long as the name is unique within the table.

create table test1
([(ticks, #, :, -,)] int, -- Given example
[~`!@.#$%^&*()_+] int) -- Top row of the keyboard.

select *
from test1|||I would be tempted to accept alpha numerics only myself just to be sure (but I am a bit of pessimist with these sorts of things). There is also the "reserved words" issue.

Actually - I imagine square brackets would need to be escaped (?).|||Sounds like dynamic creation to me...I run herd over anything getting created in oneof my databases|||Looks like only the close bracket needs to be escaped with an extra close bracket.

drop table test1
create table test1
([create table test2 (oops int not null)] int,
[]][] int)

select *
from test1

Personally, I would fight this kind of system in my shop tooth and nail, but that was not the original question.|||Looks like only the close bracket needs to be escaped with an extra close bracket.

drop table test1
create table test1
([create table test2 (oops int not null)] int,
[]][] int)

select *
from test1

Personally, I would fight this kind of system in my shop tooth and nail, but that was not the original question.

I'm old school myself, try and avoid special chars of any type, except _ in database object nams.|||I thank you all for your input.

I have fought tooth and nail My boss has said to come up with a solution. Mangers can be so OBTUSE

I have determined that the business can add alpha numeric with _
starting alpha ending alpha numeric.

Users will create these in a table that will define relationship then a process will auto generate a change control that will be added to physical structure of the database by the DBA team.

No dynamic running of an alter statment.|||Horrible.

You should rename this thread "List of managers that make SQL blow."|||I awlways wanted a list that would make my girlfr...umm never mind|||OMG Bret, if you're having trouble when she's a girlfriend...

then you are doomed after marriage...then you can get her one of those custom-lettered necklaces that say "abandon all hope, ye who..." ;)

(sorry, couldn't resist)|||Yes Brett. Listen to the advice of Mr. Romance.|||Yes Brett, listen to the advice from Mr Charm about listening to the advice from Mr Romance ;)|||Ouch! :shocked:|||i'm not sure how any of this is relavant to sql server??|||That's because you aren't familiar with the hidden intricacies of the database engines. It's actually pretty sexy in there.

list of tables and views in a database

Hi
Can someone tell me the best way to extract a list of table names and views
from a database in a select statement? I'm using SQL Server 2000.
Many thanks
Andrewhttp://www.aspfaq.com/search.asp?q=schema%3A&category=1
"J055" <j055@.newsgroups.nospam> wrote in message
news:udiRk7RSGHA.3944@.TK2MSFTNGP10.phx.gbl...
> Hi
> Can someone tell me the best way to extract a list of table names and
> views from a database in a select statement? I'm using SQL Server 2000.
> Many thanks
> Andrew
>|||That's a very useful link.
Thank you
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OPVwtCSSGHA.336@.TK2MSFTNGP12.phx.gbl...
> http://www.aspfaq.com/search.asp?q=schema%3A&category=1
>
>
>
> "J055" <j055@.newsgroups.nospam> wrote in message
> news:udiRk7RSGHA.3944@.TK2MSFTNGP10.phx.gbl...
>|||select * from information_schema.tables
"J055" <j055@.newsgroups.nospam> wrote in message
news:udiRk7RSGHA.3944@.TK2MSFTNGP10.phx.gbl...
> Hi
> Can someone tell me the best way to extract a list of table names and
> views from a database in a select statement? I'm using SQL Server 2000.
> Many thanks
> Andrew
>

List of Tables and Primary Key Foreign Key Names

Dear All,
I am trying to write some SQL that will give a list of all tables and
Primary Key/ Foreign Key constraints in a database. The code below goes part
of the way but not what I would like. It gives me:
Parent Table: Activities
Child Table: ActivitiesLocation
ForeignKey: 1
PrimaryKey: 17
TotalKeys: 1
(where 1 in the column name in the Activities table that is the Foreign Key
and column name 17 is where the Primary Key is to be found.)
Script:
SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS TotalKeys
FROM dbo.sysforeignkeys sf INNER JOIN
dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
dbo.sysobjects so2 ON so2.id = sf.fkeyid
ORDER BY so1.name
How can I get a list of the column name of column no 17 in the Activies
Table and column no 1 in the ActivitiesLocation table. Is there a better way
to get a listing of the PK/FK for a database along with table names?
Thanks again.
Alastair> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database.
http://www.aspfaq.com/search.asp?q=schema%3A|||Check out the information_schema.key_column_usage view. It should get you
started with what you need.
--Brian
(Please reply to the newsgroups only.)
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:9197A5C9-182B-451B-AE40-05DA1F98E62B@.microsoft.com...
> Dear All,
> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database. The code below goes
> part
> of the way but not what I would like. It gives me:
> Parent Table: Activities
> Child Table: ActivitiesLocation
> ForeignKey: 1
> PrimaryKey: 17
> TotalKeys: 1
> (where 1 in the column name in the Activities table that is the Foreign
> Key
> and column name 17 is where the Primary Key is to be found.)
> Script:
> SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
> Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS
> TotalKeys
> FROM dbo.sysforeignkeys sf INNER JOIN
> dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
> dbo.sysobjects so2 ON so2.id = sf.fkeyid
> ORDER BY so1.name
> How can I get a list of the column name of column no 17 in the Activies
> Table and column no 1 in the ActivitiesLocation table. Is there a better
> way
> to get a listing of the PK/FK for a database along with table names?
> Thanks again.
> Alastair
>|||Thanks Aaron and Brian for both your replies. This group is definately on th
e
ball. Exactly what I wanted.
Alastair
"Aaron Bertrand [SQL Server MVP]" wrote:

> http://www.aspfaq.com/search.asp?q=schema%3A
>
>

List of SP names and parameters

I need to come up with a list of all Stored Procedures in
a given database and the associated parameters passed,
something like this:
Proc Name parmater
proc1 a,b
proc2 a
proc3 -
I would really appreciate if somesone can shed some light
on how to do this.
Thankswww.aspfaq.com/2463
"Adel Asaad" <Adel.Asaad@.Trade-ranger.com> wrote in message
news:52f201c34195$4880d0c0$a401280a@.phx.gbl...
> I need to come up with a list of all Stored Procedures in
> a given database and the associated parameters passed,
> something like this:
> Proc Name parmater
> proc1 a,b
> proc2 a
> proc3 -
> I would really appreciate if somesone can shed some light
> on how to do this.
> Thanks|||Thank you very much. That did it - I really appreciate
the quick response.
Adel
>--Original Message--
>www.aspfaq.com/2463
>
>
>"Adel Asaad" <Adel.Asaad@.Trade-ranger.com> wrote in
message
>news:52f201c34195$4880d0c0$a401280a@.phx.gbl...
>> I need to come up with a list of all Stored Procedures
in
>> a given database and the associated parameters passed,
>> something like this:
>> Proc Name parmater
>> proc1 a,b
>> proc2 a
>> proc3 -
>> I would really appreciate if somesone can shed some
light
>> on how to do this.
>> Thanks
>
>.
>

list of databases in Analysis Services

Hi all,

I have to write a program to read the names of databases in Analysis
Services. I don't know which table I can get this information from.

Thanks a bunchHippi wrote:
> Hi all,
> I have to write a program to read the names of databases in Analysis
> Services. I don't know which table I can get this information from.
> Thanks a bunch

would this work:

select dbid, name
from master..sysdatabases
where has_dbaccess(name) = 1

--
David Rowland
http://dbmonitor.tripod.com|||No, it doesn't work. It shows all the databases I have in SQL Server

List of columns from tables across databases.

Hey guys,

Couldn't find this anywhere in google.

I want a list of all database column names for a specific table/view
from across database.

I tried this...
----------------
Select *
>From Information_Schema.Columns
----------------

I also tried this...

----------------
select syscolumns.name, sysobjects.name, * from syscolumns, sysobjects
where
sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
----------------

These queries return information about the CURRENT database.

But, if I want to do it ACROSS database or across servers.. how can I
do this?

I will express my gratitude to everyone who is kind enough to answer
this question. (I've been stuck with this problem for a while now.)

Thanks!

OhMyGaw!Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_schema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_schema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--

List of color names?

I would like to put the names of the colors into the database to allow users
to configure how they want a matrix report to appear. I don't think they
are standard HTML names are they?
Can someone point me to where can I find this list of names, or any other
advice with this endevour?
cheers,
Paul.On May 10, 8:43 pm, Paul Ritchie <REMOVEpritc...@.xtraREMOVE.co.nz>
wrote:
> I would like to put the names of the colors into the database to allow users
> to configure how they want a matrix report to appear. I don't think they
> are standard HTML names are they?
> Can someone point me to where can I find this list of names, or any other
> advice with this endevour?
> cheers,
> Paul.
The colors are the same ones that are available when building a web
application. To view the available colors, add a textbox control to a
report in Layout view. Select the textbox control. Select 'F4' (or the
'View' drop-down tab -> Properties Window) and select the drop-down
list to the right of BackgroundColor.
Regards,
Enrique Martinez
Sr. Software Consultant

List of all databases with their data & log files?

Is there an easy way to print out a simple text report of
all database names plus their internal database filenames
& logfile names plus the actual physical locations of each
file in SQL Server 2000?
I'm an Oracle admin who's just inherited an SQL Server
full of multiple databases and I need to make a structural
diagram of what all lives where inside this server. In
Oracle, a simple sql script dumps out a text list of all
these kinds of things, but all I've been able to discover
thus far in MS SQL Enterprise Manager is an unfriendly GUI
interface that makes you have to repeatedly point, click,
and browse many times over and over again and again to get
this info one tiny piece at a time, which isn't very
efficient.New to MS SQL wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
Try this:
exec sp_MSforeachDB "sp_helpdb ?"
sp_helpdb by itself will give you basic database information for all
databases
sp_helpfile will give you the files used in the currently selected
database
Passing a database name to sp_helpdb gives both results and the
sp_MSforeachDB undocumented stored procedure automatically iterates
through the list of databases on the server and generates multiple
results sets.
David G.|||Before you start getting bent out of shape over SQL Server, there an easy way
to accompish this. :-)
Open up Query Analyzer, select the Master database and type in the following
query:
select name, filename from sysdatabases
This will give you a quick list of all the database on the SQL Server
machine adn their physical location. If you want more info, let me know and
we can go from there.
Scott
"New to MS SQL" wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
>|||> select name, filename from sysdatabases
... and if on 2000, you can join sysaltfiles to get sizing information...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLScott" <SQLScott@.discussions.microsoft.com> wrote in message
news:CC55215A-B041-4370-8D87-8C4ABDBB0072@.microsoft.com...
> Before you start getting bent out of shape over SQL Server, there an easy way
> to accompish this. :-)
> Open up Query Analyzer, select the Master database and type in the following
> query:
> select name, filename from sysdatabases
> This will give you a quick list of all the database on the SQL Server
> machine adn their physical location. If you want more info, let me know and
> we can go from there.
> Scott
> "New to MS SQL" wrote:
> > Is there an easy way to print out a simple text report of
> > all database names plus their internal database filenames
> > & logfile names plus the actual physical locations of each
> > file in SQL Server 2000?
> >
> > I'm an Oracle admin who's just inherited an SQL Server
> > full of multiple databases and I need to make a structural
> > diagram of what all lives where inside this server. In
> > Oracle, a simple sql script dumps out a text list of all
> > these kinds of things, but all I've been able to discover
> > thus far in MS SQL Enterprise Manager is an unfriendly GUI
> > interface that makes you have to repeatedly point, click,
> > and browse many times over and over again and again to get
> > this info one tiny piece at a time, which isn't very
> > efficient.
> >

List of all databases with their data & log files?

Is there an easy way to print out a simple text report of
all database names plus their internal database filenames
& logfile names plus the actual physical locations of each
file in SQL Server 2000?
I'm an Oracle admin who's just inherited an SQL Server
full of multiple databases and I need to make a structural
diagram of what all lives where inside this server. In
Oracle, a simple sql script dumps out a text list of all
these kinds of things, but all I've been able to discover
thus far in MS SQL Enterprise Manager is an unfriendly GUI
interface that makes you have to repeatedly point, click,
and browse many times over and over again and again to get
this info one tiny piece at a time, which isn't very
efficient.
New to MS SQL wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
Try this:
exec sp_MSforeachDB "sp_helpdb ?"
sp_helpdb by itself will give you basic database information for all
databases
sp_helpfile will give you the files used in the currently selected
database
Passing a database name to sp_helpdb gives both results and the
sp_MSforeachDB undocumented stored procedure automatically iterates
through the list of databases on the server and generates multiple
results sets.
David G.
|||Before you start getting bent out of shape over SQL Server, there an easy way
to accompish this. :-)
Open up Query Analyzer, select the Master database and type in the following
query:
select name, filename from sysdatabases
This will give you a quick list of all the database on the SQL Server
machine adn their physical location. If you want more info, let me know and
we can go from there.
Scott
"New to MS SQL" wrote:

> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
>
|||> select name, filename from sysdatabases
... and if on 2000, you can join sysaltfiles to get sizing information...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLScott" <SQLScott@.discussions.microsoft.com> wrote in message
news:CC55215A-B041-4370-8D87-8C4ABDBB0072@.microsoft.com...[vbcol=seagreen]
> Before you start getting bent out of shape over SQL Server, there an easy way
> to accompish this. :-)
> Open up Query Analyzer, select the Master database and type in the following
> query:
> select name, filename from sysdatabases
> This will give you a quick list of all the database on the SQL Server
> machine adn their physical location. If you want more info, let me know and
> we can go from there.
> Scott
> "New to MS SQL" wrote:

List of all databases with their data & log files?

Is there an easy way to print out a simple text report of
all database names plus their internal database filenames
& logfile names plus the actual physical locations of each
file in SQL Server 2000?
I'm an Oracle admin who's just inherited an SQL Server
full of multiple databases and I need to make a structural
diagram of what all lives where inside this server. In
Oracle, a simple sql script dumps out a text list of all
these kinds of things, but all I've been able to discover
thus far in MS SQL Enterprise Manager is an unfriendly GUI
interface that makes you have to repeatedly point, click,
and browse many times over and over again and again to get
this info one tiny piece at a time, which isn't very
efficient.New to MS SQL wrote:
> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
Try this:
exec sp_MSforeachDB "sp_helpdb ?"
sp_helpdb by itself will give you basic database information for all
databases
sp_helpfile will give you the files used in the currently selected
database
Passing a database name to sp_helpdb gives both results and the
sp_MSforeachDB undocumented stored procedure automatically iterates
through the list of databases on the server and generates multiple
results sets.
David G.|||Before you start getting bent out of shape over SQL Server, there an easy wa
y
to accompish this. :-)
Open up Query Analyzer, select the Master database and type in the following
query:
select name, filename from sysdatabases
This will give you a quick list of all the database on the SQL Server
machine adn their physical location. If you want more info, let me know and
we can go from there.
Scott
"New to MS SQL" wrote:

> Is there an easy way to print out a simple text report of
> all database names plus their internal database filenames
> & logfile names plus the actual physical locations of each
> file in SQL Server 2000?
> I'm an Oracle admin who's just inherited an SQL Server
> full of multiple databases and I need to make a structural
> diagram of what all lives where inside this server. In
> Oracle, a simple sql script dumps out a text list of all
> these kinds of things, but all I've been able to discover
> thus far in MS SQL Enterprise Manager is an unfriendly GUI
> interface that makes you have to repeatedly point, click,
> and browse many times over and over again and again to get
> this info one tiny piece at a time, which isn't very
> efficient.
>|||> select name, filename from sysdatabases
... and if on 2000, you can join sysaltfiles to get sizing information...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLScott" <SQLScott@.discussions.microsoft.com> wrote in message
news:CC55215A-B041-4370-8D87-8C4ABDBB0072@.microsoft.com...[vbcol=seagreen]
> Before you start getting bent out of shape over SQL Server, there an easy
way
> to accompish this. :-)
> Open up Query Analyzer, select the Master database and type in the followi
ng
> query:
> select name, filename from sysdatabases
> This will give you a quick list of all the database on the SQL Server
> machine adn their physical location. If you want more info, let me know a
nd
> we can go from there.
> Scott
> "New to MS SQL" wrote:
>

List CRM entity attributes and data types

Hi,

I am using CRM 3.0 and have a requirement to list all the the tables, attributes names and display names and datatypes. Is there any easy way to export this information from the CRM tool or prepare a SQL query that will list the information?

You are kind of in the wrong neck of the woods, but I will try to give you a bit of help (there is a page here to go to the Microsoft Dynamics newsgroups http://www.microsoft.com/dynamics/community/newsgrouplanding.mspx)

Most of this information is available in the METABASE database in the entity and attribute tables. Not 100% sure if the display names are exactly right. They might actually be maintained in an ugly XML document.

|||Use CRM Meta Webservices. You can use the CRM SDK to get info on methods that would do this.

List Attribute Names for Single Node

Hello,

I am trying to accomplish the following goal:

"For any given node in an xml variable, return via recordset a list of attributes."

Take the following query:

DECLARE @.x xml
SET @.x = '
<Item>
<Data Key="ID" Value="1001" />
<Data Key="Name" Value="Blue" />
<Data Key="Type" Value="Color" />
</Item>'

SELECT x.value('@.Key','varchar(255)') as [Key],
x.value('@.Value','varchar(255)') as [Value]
FROM @.x.nodes('//Item/Data') Data(x)

That will return:

Key | Value
-
ID | 1001
Name | Blue
Type | Color

What I want is to be able to query @.x ahead of time such that I get back something to the effect of:

Attribute

Key
Value

The reason is that I want to be able to build the columns of my SELECT statement dynamically by iterating through the attributes of a node. I would then execute my prepared statement to get that Key/Value recordset back.

Ignoring the structure of my example XML, what I'm returning in my Key/Value recordset, and how I'm going about returning it...all I want is to know whether or not I can get via recordset a list of attributes for an XML node, and if I can, how I do it.

Thanks!
Daniel

This is a bit clumsey, but should work.

WITH AllAttr(Vals)
AS
(SELECT @.x.query('<root>{for $a in /Item/Data/@.* return <attr>{$a}</attr>}</root>'))
SELECT DISTINCT
x.value('local-name(@.*[1])','varchar(255)') as [Attribute]
FROM AllAttr
CROSS APPLY Vals.nodes('/root/attr') Data(x)

|||

For any given node, for example <Data>, you do:

declare @.x xml
SET @.x = '
<Item>
<Data Key="ID" Value="1001" />
<Data Key="Name" Value="Blue" />
<Data Key="Type" Value="Color" />
</Item>'
select distinct x.value('local-name(.)', 'varchar(20)')
from @.x.nodes('//Data/@.*') as ref(x)

List all the logins that have a certain privileges

Hi,
I would like to know how to get all the login names in a server that have a
certain privilege.
Such as I want to get all the logins that have an update privileges to a
table.
Thanks in advance
Frank
Hi,
Have a Look into sysprotects table. You could right a stored procedure to
query the sysprotects table in all the database where
a user have access.
Thanks
Hari
SQL Server MVP
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>
|||While the information is in the sysprotects table, the difficult part is
that you must go through all of the groups that the user is a member of as
well... IF the user has 3 grants on a particular table, but is a member of
a group which is denied to the table, the permission is deny...
Also remember that is the object owners are the same, and the user is denied
to the table, but granted to a view on the table, he can access the table
via the view...
So doing this gets complicated very quickly... I would search for scripts on
the web as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>

List all the logins that have a certain privileges

Hi,
I would like to know how to get all the login names in a server that have a
certain privilege.
Such as I want to get all the logins that have an update privileges to a
table.
Thanks in advance
FrankHi,
Have a Look into sysprotects table. You could right a stored procedure to
query the sysprotects table in all the database where
a user have access.
Thanks
Hari
SQL Server MVP
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>|||While the information is in the sysprotects table, the difficult part is
that you must go through all of the groups that the user is a member of as
well... IF the user has 3 grants on a particular table, but is a member of
a group which is denied to the table, the permission is deny...
Also remember that is the object owners are the same, and the user is denied
to the table, but granted to a view on the table, he can access the table
via the view...
So doing this gets complicated very quickly... I would search for scripts on
the web as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank" <wangping@.lucent.com> wrote in message
news:ehbHb51YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to know how to get all the login names in a server that have
> a
> certain privilege.
> Such as I want to get all the logins that have an update privileges to a
> table.
> Thanks in advance
> Frank
>