How to view SQL Server property information

Run the query below to find out the property information about SQL Server.
This is helpful when try to determine which features (based on the version) that are available.

 SELECT  'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
 UNION ALL  SELECT 'Collation', SERVERPROPERTY('Collation')
 UNION ALL  SELECT 'CollationID', SERVERPROPERTY('CollationID')
 UNION ALL  SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
 UNION ALL  SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
 UNION ALL  SELECT 'Edition', SERVERPROPERTY('Edition')
 UNION ALL  SELECT 'EditionID', SERVERPROPERTY('EditionID')
 UNION ALL  SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
 UNION ALL  SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
 UNION ALL  SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
 UNION ALL  SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
 UNION ALL  SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
 UNION ALL  SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
 UNION ALL  SELECT 'LCID', SERVERPROPERTY('LCID')
 UNION ALL  SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
 UNION ALL  SELECT 'MachineName', SERVERPROPERTY('MachineName')
 UNION ALL  SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
 UNION ALL  SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
 UNION ALL  SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
 UNION ALL  SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
 UNION ALL  SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
 UNION ALL  SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
 UNION ALL  SELECT 'ServerName', SERVERPROPERTY('ServerName')
 UNION ALL  SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
 UNION ALL  SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
 UNION ALL  SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
 UNION ALL  SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

If you want a very simple view of the version info run this query.
Select @@version as Version

Advertisements

How to tell what SQL objects have been added/updated recently.

Sometimes it helps to see what database objects have been added/updated recently.  If you pick up support of a new database it helps to see where the latest action has been going on. Or if you are doing development and want to make sure you have all the objects that need to get moved to the next environment.

Here is code that will return what objects have been created and when they were created *.
Just set the @dateAddedToGoBack  variable to how many days back you need.

DECLARE @dateAddedToGoBack int = 30

SELECT name, modify_date
FROM sys.objects
WHERE type IN ('P', 'V', 'U', 'PK', 'TR') --SQL_STORED_PROCEDURE, VIEW, USER_TABLE, PRIMARY_KEY_CONSTRAINT, SQL_TRIGGER
AND DATEDIFF(D,create_date, GETDATE()) < @dateAddedToGoBack


Here is code that will return what objects have been updated and when they were updated *.
Just set the @dateUpdatedToGoBack variable to how many days back you need.

DECLARE @daysUpdatedToGoBack int = 30

SELECT name, modify_date
FROM sys.objects
WHERE type IN ('P', 'V', 'U', 'PK', 'TR') --SQL_STORED_PROCEDURE, VIEW, USER_TABLE, PRIMARY_KEY_CONSTRAINT, SQL_TRIGGER
AND DATEDIFF(D,modify_date, GETDATE()) < @daysUpdatedToGoBack

* Caveat: If you are doing something like a sp_refreshview or sp_recompile it could skew your results (ie. the modify dates will be the last time run).

How to find out the last time your database was restored

Here is a short script that I got from my DBA (John Langston).
It will return the date time that your database was restored.

DECLARE @DatabaseName as varchar(50) SET @DatabaseName = ‘YOUR DATABASE NAME’

SELECT destination_database_name,max(restore_date) as restore_date from msdb..restorehistory WHERE destination_database_name = @DatabaseName GROUP BY destination_database_name

Grant execute permissions to all stored procs easily

Granting execute permissions can be stored procedures can be quite a pain, especially when you have dropped and recreated your stored procedures when moving between environments.

You can go to each stored procedure and right click each one and change the permissions.  That takes forever.

A quick way to resolve the issue (in SQL 2005 and SQL 2008) is to create a role and give it execute permissions at that database level.

Here is the SQL to run:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

 

This will create a SQL role and anyone you put in it will will have execute permissions to all stored procedures in the database.

This doesn’t work if you only want to grant execute to a subset of stored procedures.

Technorati Tags:

What’s new in SQL 2008

I talk about SQL server every chance I get because of some the great features available (SSRS, SSIS, SSAS).  So I looked into SQL 2008 the first chance I got.  More than once I have been asked about the new features of SQL server 2008.  So here is a list I complied (not 100% of the features, just the ones I thought they were easy to sell)

Top new features:

  1. T-SQL Intellisense – Intellisense is now available for T-SQL. This allows for easier and quicker development.
  2. Date/Time data types – There is a new data type for just the date and there is a new data type for just the time. There is no longer the need to program around a datetime data type.
  3. Encrypted Backups – Backups can be encrypted to prevent data disclosure or tampering.
  4. Transparent Data Encryption – The entire database can be encrypted by the SQL engine. This encrypts all database data and log files for a database log. All indexes and tables are encrypted. No changes to applications are required.
  5. Memory Management improvements in Reporting Services – Running large reports will no longer consume all available memory.

 

List of some of the new features

Security/Auditing
– Transparent Data Encryption (encryption while data is ‘still’ on disk, transparent to applications)
– External Key Management (Consolidation of key management, integration with external products)
– Data Auditing (1st-class ‘AUDIT’ objects; DDL support; audit objects, principals, data, etc.; support for multiple logging targets)

Availability/Reliability
– Pluggable CPU support
– Enhanced Database Mirroring (compression of mirror streams, enhanced performance, automatic page-level repair for principal/mirror)

Performance
– Data compression (easy to enable/disable online, more efficient data storage (this is NOT traditional data compression))
– Backup stream compression (server level control or backup statement control, all backup types)
– Performance data collection (single, common framework for data collection, reporting, and storage/warehousing)
– Improved Plan Guide support (plan freezing, pull plans directly from plan cache, SSMS integration, etc.)
– Resource Governor (create pools and groups to govern, define classifications based on built-in functions, segment resource utilization amoung groups)

Management
– Policy-based management framework (manage via policies vs. scripts, enterprise-wide support, automated monitoring/enforcement, etc.)
– Integrate with Microsoft System Center
– Extended Events (high perf lightweight tracing infrastructure, NOT sql trace, integrated with ETW, unprecidented insight into goings-on)

Development Enhancements
– Improved datetime datatypes (100th nanosecond precision (7 digits past second), time-zone datetime offset, date only, time only)
– HierarchyID datatype (hierarchical-aware data type, ORDPath values, built-in functions, methods, etc.)
– Entity Data Model support (develop ‘business entities’ vs. tables, model complex relationships, retrieve entities vs. rows/columns)
– LINQ
– Sql Server Change Tracking (Change Data Capture, get ‘diff’ data changes WITHOUT a comparible value (i.e. datetime, timestamp, etc.))
– Table Valued Parameters
– MERGE statement (‘upsert’ data, also includes deletion functionality)
– Large UDT’s (no more 8000 byte limit on CLR-based UDTs, no more 8000 byte limit for UDA’s)
– Spatial data (GEOMETRY and GEOGRAPHY data types, built-in spatial function support, spatial indexes)
– XML enhancements (support for lax validation, office 12 support, xs:dateTime support, lists/union types, LET FLOWR support, etc.)
– Inline initialization and compound assignment

  Service Broker
– New UI and Tools for working with (add/drop/edit functionality within SSMS, Diag tools, )
– Conversation Priority (set message ordering, send/receive impact, 1-10 levels)

Data Storage
– Data compression (see above)
– FILESTREAM attribute (get the ‘best of both’ functionality from BLOBs in the DB vs. BLOBs on filesystem, no more “to blob or not to blob”)
– Integrated Full Text Search (FTS fully integrated into DB engine, no external storage, no external service, more efficient and reliable costing)
– Sparse columns (more efficient storage for ‘wide’ tables with many columns that repeat and don’t contain data)
– New index types (spatial indexes, hierarchical indexes, FILTERED indexes (indexes on filtered values within columns), etc.)

Data Warehousing/ETL
– Partitioned Table Parallelism (no more thread limit per partition)
– Star Join support (no special syntax, optimizer based, full backward syntax support)
– Data compression (see above)
– Resource Governor (see above)
– Persistent Lookups in SSIS (no more re-querying for lookup operators, cache lookups in multiple ways, persist lookups to disk)
– Improved thread scheduling in SSIS (shared thread pool, pipeline parallelism)
– Change Data Capture (see above)
– MERGE statement (see above, great uses with slowly changing dimensions)
– Scale-out analysis services (read-only storage supports multiple AS servers)
– Subspace computations
– New Tools for Cube design
– Best Practice Design Alerting
– Backup cubes with better scalability
– Data-mining add-ins for Excell

Reporting
– IIS Agnostic Reporting Services Deployment (no IIS required to run RS any longer)
– Rich-text support
– Enhanced visualiztion (graphing)
– New Word rendering (render reports to Microsoft Word)

Record count of all tables in a database

Here is a sql script that will give a count of all records in every table in
your database.

This comes from my friend David McCollough.

CREATE TABLE #sizes ([name] nvarchar(200), 
             [rowcount] varchar(25))
DECLARE @tablename VARCHAR (128)

DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
   order by TABLE_NAME

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
  --faster than select count(*)
  exec ('Insert into #sizes Select ''' +  @tablename + ''' as [name],(SELECT rows FROM sysindexes WHERE id = OBJECT_ID(''' +  @tablename + ''') AND indid < 2)')
FETCH NEXT
   FROM tables
   INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

select * from #sizes Order by [name]
drop table #sizes

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Search for column name across all tables

Here is another sql script I got from my friend David McCollough that will allow you to search all tables for a specific field name.

Just set the @ColumnName variable and let ‘er rip.

DECLARE @ColumnName       varchar(1000)

SET @ColumnName = 'PlayerId'

    select o.name as TableName,
           c.name as [ColumnName]
      from sysobjects o 
inner join syscolumns c on c.id = o.id
      where o.type = 'u' and c.name = @ColumnName       
   Order by TableName

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Technorati Tags:

Dynamic searches without dynamic sql

Search functionality is a feature that comes up with many applications.  I have written more than one search stored procedure (and screen), but I usually ended up with writing dynamic sql (building a SQL statement and using the exec command, see Figure 1 below).  The main disadvantage to dynamic sql statements is that they are not compiled and performance always matters.

I was reading CoDe magazine one day and noticed a part of an article that mentioned dynamic sql without dynamic sql.  I read it and was quite pleased with the approach.  So the basic approach is to have all the lookup parameters as optional parameters, set null parameters to the % wildcard, use a series of like statements in the where clause.

See figure 2 for an example of the sql code.

Figure 1

Create Procedure Search
    @ProductName VarChar(100)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @ProductName +')'

Exec ( @SQL)

Figure 2
Create Procedure Search
@ProductName varchar(100) = null
As
Set @ProductName = isnull(@ProductNmae, '%')

Select * from Products
where ProductName like @ProductName

Sql Server Keyword search across Stored Procedures, Functions, Triggers

Here is some T-SQL I got from a friend of mine (David Mccoullgh).
Just set the @SearchPhrase to search across stored proc, functions, triggers, views to see where that string is in use.

Very helpful if you are changing a field name and want to know where it is in use.

DECLARE @SearchPhrase varchar(1000)

SET @SearchPhrase = ‘ProductCode’

SELECT DISTINCT sysobjects.name AS [Object Name],
case
when sysobjects.xtype = ‘P’ then ‘Stored Proc’
when sysobjects.xtype = ‘TF’ then ‘Function’
when sysobjects.xtype = ‘TR’ then ‘Trigger’
when sysobjects.xtype = ‘V’ then ‘View’
end as [Object Type],
(SELECT ParentTable.[Name]
FROM sysobjects ParentTable
WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable
FROM sysobjects, syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in (‘P’,’TF’,’TR’, ‘V’)
AND sysobjects.category = 0
AND CHARINDEX(@SearchPhrase, syscomments.text) > 0