Quickly read company specific table data for each company with SQL

RemkoD
RemkoD Member Posts: 100
edited 2018-03-09 in SQL Tips & Tricks
Sometimes I need to check table data from a company specific table in each company. A quick lookup from MSSQL can make live easier. I've made an easy query for this.

In this example I want a quick overview from all the job queues and entries in the NAV database.
DECLARE @TableName varchar (50) = 'Job Queue'
DECLARE @TableName2 varchar (50) = 'Job Queue Entry'

SELECT 
	'SELECT ''' + [Name] + ''' AS "NAV Company ('+ [Name] +')", * FROM [dbo].[' + REPLACE([Name], '.', '_') + '$' + @TableName + ']' AS "Generated Query 1", 
	'SELECT ''' + [Name] + ''' AS "NAV Company ('+ [Name] +')", * FROM [dbo].[' + REPLACE([Name], '.', '_') + '$' + @TableName2 + ']' AS "Generated Query 2"
FROM [dbo].[Company]

This query generates new queries.

vd3fddy82ut3.png

You can execute all generated queries to get the table data from all the companies. If you change the table name in the declare statement you can use this query for other tables as well.

Answers

  • RemkoD
    RemkoD Member Posts: 100
    It wasn't a question. I thought it was a handy query to share. It would've saved me a few minutes if I had found an example. Isn't that where the SQL Tips & Tricks topic is for?

Categories