Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Answered

how to differentiate a database system with one application through a SQL query?

Posted on by Microsoft Employee

hello

I need to know how can i get all database applicaction and all databases systems installed on Dynamics SL 2011 by SQL query.

I make the query: "select * from sys.databases", and I get all databases installed on my server.

  1. IEXPSYS
  2. SYSVT
  3. TELDATAAPP
  4. 0101CONSTRUCAPP

but I don't know wich data can make the filter to get by type (system or application)

 

I need this result:

System:

  1. IEXPSYS
  2. SYSVT

Application:

  1. TELDATAAPP
  2. 0101CONSTRUCAPP

 

 

Thanks vey much 

Greetings.

  • Verified answer
    Erich Strelow F Profile Picture
    Erich Strelow F 1,642 on at
    RE: how to differentiate a database system with one application through a SQL query?

    There's no single query to produce this. You've got to have some scripting or procedure as far as I know. This because of MSSQL design.

    Having said so, there are some macro oriented sp built in SQL. The closest thing to a one liner may be something like:

    sp_msforeachdb 'select distinct "?" AS dbsys, DatabaseName as dbapp from [?].dbo.Company'


    This will produce sys database and app database matching pairs along with an awful lot of errors. If you are using SQL Mgmt Studio, set the outoput to text for beter results. Beware that we are not really scanning APP databases, they appear in the result because they are mentioned by SYS ones. In your case, the output would be something like:

    Invalid object name 'TELDATAAPP.dbo.Company'.
    dbsys    dbapp
    -------- ------------------------------
    IEXPSYS  0101CONSTRUCAPP
    
    Invalid object name '0101CONSTRUCAPP.dbo.Company'.
    dbsys  dbapp
    ------ ------------------------------
    SYSVT  TELDATAAPP
    


Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,537 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,520 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans