Sql databases

[srikar38]

Hi.
my name is srikar.......and my query is

How to differentiate system databases and user created databases in sql server 2005??????????????

[kvprajapati]

>How to differentiate system databases and user created databases in sql server

Read these articles - http://articles.techrepublic.com.com/5100-10878_11-6105067.html and http://www.databasejournal.com/features/mssql/article.php/1460181/System-Databases-and-Tables.htm

[finito]

Hi.
my name is srikar.......and my query is

How to differentiate system databases and user created databases in sql server 2005??????????????

its pretty simple,
But I color coded it for you

Red is System
and
Purple is for User

[srikar38]

I need a query to get only user created databases ..............................

[mith_cool]

i think it is easy to find out user created databases as they are four in numbers namely model, master, Tempdb, msdb and Distribution database in created while sql server instance is configured as a distribution for replication. and i think there is no such query for aforesaid question

[kvprajapati]

SELECT * FROM SYS.DATABASES

Read this.

[finito]

adatapost's code will give you all the databases

I noticed that collation_name value is null for user databases. This may help you.

Select * From SYS.Databases where collation_name is Null

[srikar38]

adatapost's code will give you all the databases

I noticed that collation_name value is null for user databases. This may help you.

Select * From SYS.Databases where collation_name is Null

Its not working ............................

[srikar38]

its not working ............................

i t s not working..................................................

[finito]

is this working?

Select * From SYS.Databases

if it is then see where there is something usable in the fields.

[babbu]

SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_Catalog = 'myDatabase'

i googled your query and came up with this solution. Sorry couldn't verify it cause i do not have access to a database at this moment

[srikar38]

is this working?

Select * From SYS.Databases

if it is then see where there is something usable in the fields.

No.......
already i tried this..............no useful...........

[finito]

No.......
already i tried this..............no useful...........

Ok hmm, the problem is your pc then.

Can you take a screenshot of your PC where your entering this code?

and if you haven't tried the code in SQL Server Management Studio Express try it there and post a screenshot form there as well

[srikar38]

is this working?

Select * From SYS.Databases

if it is then see where there is something usable in the fields.

its not working............already i tried................

[finito]

I know you tried that you already said that?

post the screenshots please.

[srikar38]

Click this link to view snapshot http://www.4shared.com/photo/80wRey58/untitled.html

[finito]

take screen shot of SELECT * FROM INFORMATION_SCHEMA.Tables

[srikar38]

First read my question................ i am not asking tables................

[kvprajapati]

select * from 
  sys.databases  
    where 
     name not in (N'master', N'tempdb', N'model', N'msdb')

[srikar38]

i know this query................i dont wnt to compare with system databases.........

[finito]

... Can you tell us what exactly do you want to do?

[srikar38]

i want a ""QUERY"" to identify system databases in sqlserver 2005

[finito]

to do what?

[srikar38]

to do what?

just i wnt know is there any query exists or not.............

if i wnt know abt user creatd databases in my server i must know abt system created databases............wat is this logic!!!!!!!!???????

[kvprajapati]

>just i wnt know is there any query exists or not.............

Yes. There are number of ways and some of them are posted here. For more information google please. You can also get the list of databases by its owner type. (System database are created by 'sa' user)