Checking that data has been created in multiple tables
As I mention in this other SQL answer:


  Any SQL query has up to eight primary clauses.  WITH, SELECT, INTO, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Everything else is a part of one of these clauses. These 8 clauses should have zero indentation relative to the query as a whole, and everything else should be indented by at least one level.


A little indentation can go a very long way to making your query far more readable.

So, just using your first SELECT as an example, proper indentation would look something like this:

SELECT @rowCntAttCode = SUM(PART.rows)
FROM sys.tables TBL
    INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
    INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttCode AND IDX.index_id &lt; 2
GROUP BY TBL.object_id, TBL.name;


If you insist on turning any of these single lines into multiple lines to reduce the width, then the extra lines should get an extra level of indentation.  For example

SELECT @rowCntAttCode = SUM(PART.rows)
FROM sys.tables TBL
    INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
    INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
        AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttCode 
    AND IDX.index_id &lt; 2
GROUP BY TBL.object_id, TBL.name;


And some people like breaking the ON part of a join onto its own line, which should look like this:

SELECT @rowCntAttCode = SUM(PART.rows)
FROM sys.tables TBL
    INNER JOIN sys.partitions PART
        ON TBL.object_id = PART.object_id
    INNER JOIN sys.indexes IDX 
        ON PART.object_id = IDX.object_id
            AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttCode 
    AND IDX.index_id &lt; 2
GROUP BY TBL.object_id, TBL.name;


Any of these three indention styles are fine, but be consistent, no matter what you pick.  Hopefully, you can see how this sort of indention style helps guide the reader's eye to the important parts of the query.



Now, with the indentation out of the way... I'm a little concerned about how exactly your query is even working.

Normally, when we're doing a variable assignment in a select, we want to use TOP 1 just to be certain we're only getting one row back.  Given that you're using an aggregation query, you don't necessarily need to do this... but why are you using a GROUP BY?  What sort of results do you expect?  The TBL.name is redundant in the GROUP BY since you already have a WHERE TBL.name = @tblAttCode, so there can only be one value in that column anyway.  What are you hoping to accomplish with the GROUP BY TBL.object_id?