0

I have the following powershell command which displays the number of rows in a sql table. This works but it also includes the header:

Invoke-Sqlcmd -ServerInstance "01-SQL1\INSTANCE22" -Database master -Query "select Count(*) from dbo.mytable"

The Output I get is:

Column1
-------    
2450 <= This line is actually displayed under Column1 on a separate line. I just couldn't get it to display properly here.

The only output I need is to show me the row count if it's greater than X number of rows, otherwise print "Less than 5000 rows found." How do I do this?

yorkman
  • 119
  • 2
  • 16
  • 2
    Either pipe to `| Select-Object -ExpandProperty Column1` -or- enclose your command in parentheses and append `(Invoke-Sqlcmd ...).Column1` –  Jan 31 '19 at 18:54

3 Answers3

1

You're trying to mix numbers with text, so your best bet is to return that value into a variable and then set a rule to display whatever you want: however, you can also accomplish this with pure SQL:

SELECT CASE
           WHEN COUNT(*) < 5000 THEN
               'Less than 5,000'
           ELSE
               CAST(COUNT(*) AS VARCHAR(10))
       END
FROM [dbo].[myTable] ;
Russell Fox
  • 5,273
  • 1
  • 24
  • 28
  • How would you put that together with the command I included so that it's a one liner...perhaps with @" and "@ ? – yorkman Jan 31 '19 at 19:18
  • You can just strip out the white space - I only added it so it's easier to see what's going on: SELECT CASE WHEN COUNT(*) < 5000 THEN 'Less than 5,000'ELSE CAST(COUNT(*) AS VARCHAR(10)) END FROM [dbo].[myTable] ; – Russell Fox Jan 31 '19 at 19:38
  • FYI, the SQL ANSII standard is to not require white space other than spaces: any SQL code can be reduced to a single line, as long as you don't have two dashes for a comment in there (--). And if you do need a comment, use the /*...*/ multi-line comment format, instead. – Russell Fox Jan 31 '19 at 19:40
0

Select count(*) as totalcount from table

Mohammad Shehroz
  • 226
  • 2
  • 11
0

this should answer your question. sqlcmd -S localhost -d master -Q "if ((select Count(*) from spt_values) < 5000) PRINT 'Less than 5000 rows found'"

ps version: just thew query that's different

Invoke-Sqlcmd -ServerInstance "localhost" -Database master -Query "if ((select Count(*) from spt_values) < 5000) PRINT 'Less than 5000 rows found'" -verbose

Daniel N
  • 1,122
  • 1
  • 8
  • 14