Tuesday, April 30, 2019

Quick count on all SQL Server Object Types

How many times have you copied objects from database A to B, and needed to compare object counts when complete?  Or, maybe you just want to see what's in one database that is not in another?  Here's a quick piece to give you object type counts in your database, and another to show you what's in one database that's not in the other.

-- object type counts
SELECT
       'Count' = COUNT(*),
       s.Type,
       'Description' = CASE [Type]
       WHEN 'AF' THEN 'Aggregate function (CLR)'
       WHEN 'C' THEN 'CHECK Constraint'
       WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
       WHEN 'EC' THEN 'Edge Constraint'
       WHEN 'F' THEN 'FOREIGN KEY Constraint'
       WHEN 'FN' THEN 'Scalar functions'
       WHEN 'IF' THEN 'SQL Inline Table-valued Function'
       WHEN 'IT' THEN 'Internal table'
       WHEN 'P' THEN 'SQL Stored Procedure'
       WHEN 'PK' THEN 'Primary Key'
       WHEN 'R' THEN 'Rule (old-style, stand-alone)'
       WHEN 'RF' THEN 'Replication-filter procedure'
       WHEN 'S' THEN 'System base table'
       WHEN 'SN' THEN 'Synonym'
       WHEN 'SO' THEN 'Sequence  Object'
       WHEN 'SQ' then 'Service Queue'
       WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
       WHEN 'TF' THEN 'SQL table-valued-function'
       WHEN 'TR' THEN 'SQL DML trigger'
       WHEN 'TT' THEN 'Table type'
       WHEN 'UQ' THEN 'UNIQUE Constraint'
       WHEN 'U' THEN 'User Table'
       WHEN 'V' THEN 'View'
       WHEN 'X' THEN 'Extended stored procedure'
       ELSE type END
FROM
       sys.objects s
GROUP BY
       s.type
ORDER BY

       s.type

Here's the output from my AdventureWorks2012 database:           
                

And this piece will tell you what tables are in the database you are connected to, that is not in the 'DatabaeName' on the other 'ServerName' in the query:  

SELECT a.name FROM sys.objects a
WHERE a.type = 'U'
AND NOT EXISTS(
       SELECT 1 FROM ServerName.DatabaseName.sys.objects b
       WHERE b.type = 'U'
       AND a.name = b.name
       )


Of course, you can change it to any object type, or even all objects... and you'll need to have a linked server for 'ServerName' in order for it to work.

Hope it helps!



Sunday, April 28, 2019

How to find all startup stored procedures?

I upgraded an instance from v2008 to v2014 over the weekend.  Fortunately, everything went well, but this was one of the prerequisites:

  • Disable all startup stored procedures, as the upgrade process will stop and start services on the SQL Server instance being upgraded. Stored procedures processed at startup time might block the upgrade process.

So I ask myself -- how do I find all startup procs?  Very simple.  Run this query to identify what procedures are configured to run at startup:

  -- find procs that run at startup
  SELECT name,create_date,modify_date
  FROM sys.procedures
  WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1


And run this to tell the system to run it at startup, or not:

  -- edit to run at startup, or not
  EXEC sp_procoption
   @procname = 'usp_MssqlServiceStart',@optionname= 'startup',@optionvalue = 'off'



As I said, very simple.  Take a look at this for more details on sys.procedures:

And even this for more details on the SQL Server upgrade:


Thursday, April 25, 2019

How to find out when SQL Server was installed?

Good question.  How do you find out when SQL Server was installed?  I believe there may be a few different methods but the one that I use is pretty simple.  I just query the system for something that is created at the time of the install --- the NT AUTHORITY\SYSTEM login.  Pretty easy... as you can see here.


       -- SERVERPROPERTY
       SELECT
              SERVERPROPERTY('productversion') ProductVersion,
              SERVERPROPERTY ('productlevel') ProductLevel,
              SERVERPROPERTY ('edition') Edition,
              SERVERPROPERTY ('MachineName') MachineName,
              create_date 'SQL Server Installation Date'
       FROM
              sys.server_principals
       WHERE
              name='NT AUTHORITY\SYSTEM'


Here's the output from one of my instances:


And that's that!



Monday, April 22, 2019

How to disable or re-enable table constraints

Say you are copying data to one table from another, and you need to work around a  particular FOREIGN KEY constraint.  Or, maybe you you need to work around ALL FK constraints on that table.  We'll often need to work around FK constraints in situations like these.  This post is just a quick how-to, for a single table constraint, or all constraints for a table.


For all constraints on a table, replace 'TableName' with your table name:

       -- Disable all table constraints, whole database
       ALTER TABLE TableName NOCHECK CONSTRAINT ALL

       ------ do your table insert here

       -- Enable all table constraints, whole database
       ALTER TABLE TableName CHECK CONSTRAINT ALL 


For a single constraint on a table, replace 'TableName' and 'ConstraintName' with your specific object names:

       -- Disable single constraint, single table
       ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

       ----- do your table insert here

       -- Enable single constraint, single table
       ALTER TABLE TableName CHECK CONSTRAINT ConstraintName



There you have it.