Skip to main content

Notifications

Microsoft Dynamics SL (Archived)

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

(0) ShareShare
ReportReport
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.

*This post is locked for comments

  • 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

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans