Welcome

Powered By:

Blog Stats

  • Blogs - 7
  • Posts - 937
  • Articles - 1
  • Comments - 1372
  • Trackbacks - 90

Bloggers (posts, last update)

Latest Posts

Using C# to interface with SQLite

If you need to interface with SQLite there are a couple of steps.

1) You need to get the .net provider for SQLite from sourceforge.net
2) Then add a reference to System.Data.SQLite to your project.
3) You need to make sure the reference is marked to be copied locally.

Here is a C# class to for doing select, insert, update, and delete.

 public class SqlLiteHelper
    {
        String dbConnection;

        /// <summary>
        ///     Single Param Constructor for specifying the DB file.
        /// </summary>
        /// <param name="inputFile">The File containing the DB</param>
        public SqlLiteHelper(String inputFile)
        {
            dbConnection = String.Format("Data Source={0}", inputFile);
        }

        /// <summary>
        ///     Single Param Constructor for specifying advanced connection options.
        /// </summary>
        /// <param name="connectionOpts">A dictionary containing all desired options and their values</param>
        public SqlLiteHelper(Dictionary<String, String> connectionOpts)
        {
            String str = "";
            foreach (KeyValuePair<String, String> row in connectionOpts)
            {
                str += String.Format("{0}={1}; ", row.Key, row.Value);
            }
            str = str.Trim().Substring(0, str.Length - 1);
            dbConnection = str;
        }


        /// <summary>
        ///     Allows the programmer to run a query against the Database.
        /// </summary>
        /// <param name="sql">The SQL to run</param>
        /// <returns>A DataTable containing the result set.</returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();
                    using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        using (SQLiteDataReader reader = mycommand.ExecuteReader())
                        {
                            dt.Load(reader);
                            reader.Close();
                        }
                    }
                    cnn.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return dt;
        }

        /// <summary>
        ///     Allows the programmer to interact with the database for purposes other than a query.
        /// </summary>
        /// <param name="sql">The SQL to be run.</param>
        /// <returns>An Integer containing the number of rows updated.</returns>
        public int ExecuteNonQuery(string sql)
        {
            int rowsUpdated = 0;

            using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
            {
                cnn.Open();
                using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                {
                    mycommand.CommandText = sql;
                    rowsUpdated = mycommand.ExecuteNonQuery();
                }
                cnn.Close();
            }
            return rowsUpdated;
        }

        /// <summary>
        ///     Allows the programmer to retrieve single items from the DB.
        /// </summary>
        /// <param name="sql">The query to run.</param>
        /// <returns>A string.</returns>
        public string ExecuteScalar(string sql)
        {
            using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
            {
                cnn.Open();
                using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                {
                    mycommand.CommandText = sql;
                    object value = mycommand.ExecuteScalar();
                    cnn.Close();
                    if (value != null)
                    {
                        return value.ToString();
                    }
                }
            }
            return "";
        }

        /// <summary>
        ///     Allows the programmer to easily update rows in the DB.
        /// </summary>
        /// <param name="tableName">The table to update.</param>
        /// <param name="data">A dictionary containing Column names and their new values.</param>
        /// <param name="where">The where clause for the update statement.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Update(String tableName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }
            try
            {
                this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
            }
            catch
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily delete rows from the DB.
        /// </summary>
        /// <param name="tableName">The table from which to delete.</param>
        /// <param name="where">The where clause for the delete.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Delete(String tableName, String where)
        {
            Boolean returnCode = true;
            try
            {
                this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
            }
            catch (Exception ex)
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily insert into the DB
        /// </summary>
        /// <param name="tableName">The table into which we insert the data.</param>
        /// <param name="data">A dictionary containing the column names and data for the insert.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Insert(String tableName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            Boolean returnCode = true;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
            }
            catch (Exception ex)
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily delete all data from the DB.
        /// </summary>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearDB()
        {
            DataTable tables;
            try
            {
                tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
                foreach (DataRow table in tables.Rows)
                {
                    this.ClearTable(table["NAME"].ToString());
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        ///     Allows the user to easily clear all data from a specific table.
        /// </summary>
        /// <param name="table">The name of the table to clear.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearTable(String table)
        {
            try
            {

                this.ExecuteNonQuery(String.Format("delete from {0};", table));
                return true;
            }
            catch
            {
                return false;
            }
        }
    }

posted @ 11/15/2013 10:44 AM by Chris Barba

Using PowerShell to Check AD Schema

Here we are, a cold crisp 20 degree Wednesday in November. I thought to myself…this is not cool (no pun), but you know what is cool?  Yeah, I’m sure you guessed PowerShell’s ActiveDirectory module.

Just a quick blog note to show how PowerShell quickly settled a dispute during an upgrade of our AD schema to handle a Windows 2012 DC. Of course this wasn’t a big dispute, many other tools could have been used. The question was had the Schema already been changed to support a 2012 Server. Again, there are many tools that could provide the answer, but what made this so cool was being able to share the experience with others who didn’t know PowerShell could replace some of the old stand by AD tools. So this is more of an AH-HA moment that felt right to share (and the script)…All brought to us by PowerShell and the ActiveDirectory module.

(An academic honesty note here…this script is not 100% my own work...More like 5% – 10% my work, I can’t remember where I snagged the meat of this script so the credit remains unknown.)

#This script will query AD for the Schema Version of AD,Exchange and Lync. Can be ran as least privilaged user.

Import-Module ActiveDirectory

#Array

$SchemaVersions = @()

#AD Portion

$SchemaHashAD = @{

13="Windows 2000 Server";

30="Windows Server 2003";

31="Windows Server 2003 R2";

44="Windows Server 2008";

47="Windows Server 2008 R2";

56="Windows Server 2012"

}

$SchemaPartition = (Get-ADRootDSE).NamingContexts | Where-Object {$_ -like "*Schema*"}

$SchemaVersionAD = (Get-ADObject $SchemaPartition -Property *).objectVersion

$AdSchema = New-Object System.Object

$AdSchema | Add-Member -Type NoteProperty -Name Schema -Value $SchemaVersionAD

$AdSchema | Add-Member -Type NoteProperty -Name Product -Value "AD"

$AdSchema | Add-Member -Type NoteProperty -Name Version -Value $SchemaHashAD.Item($SchemaVersionAD)

$SchemaVersions += $AdSchema

#Exchange Portion

$SchemaHashExchange = @{

4397="Exchange Server 2000 RTM";

4406="Exchange Server 2000 SP3";

6870="Exchange Server 2003 RTM";

6936="Exchange Server 2003 SP3";

10628="Exchange Server 2007 RTM";

10637="Exchange Server 2007 RTM";

11116="Exchange 2007 SP1";

14622="Exchange 2007 SP2 or Exchange 2010 RTM";

14726="Exchange 2010 SP1";

14732="Exchange 2010 SP2";

15137="Exchange 2013"

}

$SchemaPathExchange = "CN=ms-Exch-Schema-Version-Pt,$SchemaPartition"

If (Test-Path "AD:$SchemaPathExchange") {

$SchemaVersionExchange = (Get-ADObject $SchemaPathExchange -Property rangeUpper).rangeUpper

}

Else {

$ExchangeErr = 1

}

$ExchSchema = New-Object System.Object

$ExchSchema | Add-Member -Type NoteProperty -Name Schema -Value $SchemaVersionExchange

$ExchSchema | Add-Member -Type NoteProperty -Name Product -Value "Exchange"

$ExchSchema | Add-Member -Type NoteProperty -Name Version -Value $SchemaHashExchange.Item($SchemaVersionExchange)

If ($ExchSchema.Schema -ne 0) {

$SchemaVersions += $ExchSchema

}

#Lync Portion

$SchemaHashLync = @{

1006="LCS 2005";

1007="OCS 2007 R1";

1008="OCS 2007 R2";

1100="Lync Server 2010";

1150="Lync Server 2013"

}

$SchemaPathLync = "CN=ms-RTC-SIP-SchemaVersion,$SchemaPartition"

If (Test-Path "AD:$SchemaPathLync") {

$SchemaVersionLync = (Get-ADObject $SchemaPathLync -Property rangeUpper).rangeUpper

}

Else {

$LyncErr = 1

}

$LyncSchema = New-Object System.Object

$LyncSchema | Add-Member -Type NoteProperty -Name Schema -Value $SchemaVersionLync

$LyncSchema | Add-Member -Type NoteProperty -Name Product -Value "Lync"

$LyncSchema | Add-Member -Type NoteProperty -Name Version -Value $SchemaHashLync.Item($SchemaVersionLync)

If ($LyncSchema.Schema -ne 0){

$SchemaVersions += $LyncSchema

}

#OutPut Section

Write-Host "Known current schema version of products:"

$SchemaVersions | Format-Table * -AutoSize

#I think this error handling is probably better off in the setting of the note property but this takes care of it for now

If ($LyncErr -eq 1){

Write-Host "Lync or OCS not present" -ForegroundColor Yellow

}

If ($ExchangeErr -eq 1){

Write-Host "Exchange not present" -ForegroundColor Yellow

}

#---------------------------------------------------------------------------><>

So there you have it, another way PowerShell rocks.

posted @ 11/13/2013 10:22 AM by Greg Tate

Hiding Disabled Users From Exchange Address Book

The other day while reviewing an Exchange 2010 Environment, I noticed a few active mailboxes belonging to disabled users. For obvious reasons this isn’t a good thing, if for nothing else it clutters up the Exchange Address Book.

Next thought in my mind…So what’s the best way to hide these disabled users? Having the PowerShell bias that I do in fact have, I had to spend 15 minutes reviewing the options.

  1. Use a manual process. This would include disabling the user in AD, followed up with the steps described here.
  2. Use Exchange Address Book Policies(ABP). As indicated in this article, APB’s have a dependency on Exchange 2010 SP2. That said it seems like a viable and interesting approach.
  3. Use PowerShell. As I started from the outset, I’m biased right now…So a PowerShell only approach seems “more better”.

Here is the script I used in a resource / user environment. Keep in mind this is a down and dirty version, a proof of concept. I would limit the use of this example as an inspiration only. (good or bad)

#This script will query for all LinkedMailboxes when ran on an Exchange Server

#It will return a user set who show their Linked Master Accounts as disabled

#Use the results with "Set-Mailbox -HiddenFromAddressListsEnabled $true" to change

#all of the disabled users to hidden from the address book. Example Below

add-pssnapin Microsoft.Exchange.Management.PowerShell.E2010 -ErrorAction Continue

Import-Module ActiveDirectory

$linkmbx = get-mailbox -RecipientTypeDetails LinkedMailbox

$alcusers = Get-Aduser -Filter * -Server <your domain here> -Properties Enabled

$userrpt = @()

foreach ($mbx in $linkmbx){

$name = $mbx.linkedmasteraccount

$user = $name.split("\")

$alcuser = $alcusers | where {$_.samaccountname -eq $user[1]}

if ($alcuser.Enabled -eq $false){

$rpt = New-Object System.Object

$rpt | Add-Member -MemberType NoteProperty -Name Name -Value $alcuser.Name

$rpt | Add-Member -MemberType NoteProperty -Name Alias -Value $mbx.alias

$rpt | Add-Member -MemberType NoteProperty -Name HidFromAddBook -Value $mbx.HiddenFromAddressListsEnabled

$userrpt += $rpt

$rpt

}

}

Write-Host "There are" $userrpt.count "linked mailboxes with disabled user accounts in user domain"

<#

#Uncomment this section if you want to include changing the address book visability

Foreach ($user in $usrrpt){

Write-Host "Changing address book visability for" $user.alias

Set-Mailbox -Identity $user.alias -HiddenFromAddressListsEnabled $true

}

#>

Of course the next thought of automation comes to mind…but that’s a different post. 

posted @ 11/12/2013 3:15 PM by Greg Tate

How to get a list of all identity columns in a database

Here is some code to get a list of identity columns in database.

select  so.name as TableName,  + o.list as IdentityColumnName 
from    sysobjects so
cross apply
    (SELECT 
        column_name
         + 
           case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        ''
        end + ' '
     from information_schema.columns where table_name = so.name
    ) o (list)
where   xtype = 'U'
AND o.list is not null
AND name    NOT IN ('dtproperties')

posted @ 11/11/2013 4:14 PM by Chris Barba

Check if a database exists on a server

Here is some code to use to check if a database exists.
Just replace the string 'DATABASE NAME’.

SELECT * FROM [master].[sys].[databases] WHERE name='DATABASE NAME'

posted @ 11/11/2013 4:11 PM by Chris Barba

How to switch Entity Framework database connected to

If you have a connection to a database through entity framework and you need to switch it to another database (with the exact same structure) you just need to set the Connection.ConnectionString (as seen below).
I had an application where we created a copy of the Master database when setting up a new client.  So using Entity Framework I switched from the master database to the client (depending on what the admin was doing).

using (MasterEntities aEntities = new MasterEntities())
{

//Switch db connected to

aEntities.Database.Connection.ConnectionString = aEntities.Database.Connection.ConnectionString.Replace("OldDatabaseName", "NewDatabaseName"); //Some Query

}

posted @ 11/11/2013 4:01 PM by Chris Barba

Create SharePoint 2013 Result Source with PowerShell

In my continued automation efforts, I was looking to convert documentation provided from a consultant into something more…well…automated. In this first of four parts I’ll list out creating a Result Source with Powershell.

Subsequent posts (part 2 – 4) will give example of creating Result Types, Query Rules, and Search Navigation. The aim of this effort is to make use of PowerShell in rebuilding, essentially cloning without data, a search service application. This is useful when Microsoft support gives the classic solution of “rebuild” your service application. Doh!

It should be noted:

  • This will create the Result Source at the Site Collection level.
  • This isn't 100% my original work, it’s inspired (taken mostly and modified) from the SearchGuys blog post.
    • The blog had Bing and Federation as an example, this example is a local SharePoint Result Set to Query BCS

 

Add-PSSnapin Microsoft.SharePoint.PowerShell

#Change These Variables To Fit

$SPWeb = "Your SP Site Collection Here"

$resultSourceName = "Your Content Source Friendly Name Here"

$resultSourceDescription = "Description for (BCS) Data Source"

$qT = '{searchTerms?} (ContentSource="<Content Source Friendly Name Here>" IsContainer=false)'

#Begin The Process

$ssa = Get-SPEnterpriseSearchServiceApplication

$fedman = New-Object Microsoft.Office.Server.Search.Administration.Query.FederationManager($ssa)

$searchOwner = Get-SPEnterpriseSearchOwner -SPWeb $SPWeb -Level SPSite

$resultSource = $fedman.GetSourceByName($resultSourceName, $searchOwner)

#Check To See if it exists

if(!$resultSource){

Write-Host "Result source does not exist. Creating."

$resultSource = $fedman.CreateSource($searchOwner)

}

else { Write-Host "Using existing result source." }

#Finish It Up

$resultSource.Name =$resultSourceName

$resultSource.ProviderId = $fedman.ListProviders()['Local SharePoint Provider'].Id

$resultSource.Description = $resultSourceDescription

$resultSource.CreateQueryTransform($qT)

$resultSource.Commit()

posted @ 11/7/2013 7:55 PM by Greg Tate

Working with SharePoint Web Parts using PowerShell

First things first. I’m not a developer. I seem to do ok working my way through the SharePoint object model with PowerShell and writing automation scripts, but that doesn’t make me a developer.

With that out the way, I do find myself in an odd place with developers neglecting (for whatever reason) to automate population of web parts in the content pages their solution has deployed. An example might be Search Content web part needing to have the proper display template selected for displaying conversations. Ah…My Dev friends (don’t hate me) but why not go that extra mile?  If I can do it through script, surely you can employ your superior coding skills to include it in the solution (wsp)!

For those of you who may find yourself in my shoes, here is a script I created to help ease that cross farm (environment pain). Essentially the script is a function with a few parameters. This is the core, from here you can customize to your needs. It’s a great starting point for anyone who wants to automate changes to web parts via PowerShell.  You can copy and paste the script below into PowerShell ISE and save it to whatever name you like

 

Add-PSSnapin Microsoft.SharePoint.Powershell

#----Start of Function-------

Function Set-WebParts {

[CmdLetBinding()]

Param(

[Parameter(Mandatory=$True,Position=0)]

[String]$SiteUrl = $(Read-Host "Please Enter Site URL"),

[Parameter(Mandatory=$True,Position=1)]

[String]$PageUrl = $(Read-Host "Please Enter Page URL")

)

$web = Get-SPWeb $SiteUrl

#+Get and Checkout Page

#+-Get Page

$page = $web.GetFile($pageURL)

#+-CheckOut The Page

$page.CheckOut()

#+-Load Limited Web Part Manager

$wpm = $web.GetLimitedWebPartManager($pageURL, [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)

#+Change Conversations WebPart - In my example I had some webparts titled "Conversations" and some Titled "Content Search"

$wp = $wpm.WebParts | Where {$_.Title -eq "Conversations" -or $_.Title -eq "Content Search"}

#+-This is the base url for the template - Change this to whatever meets the need

$base = "~sitecollection/_catalogs/masterpage/Display Templates/Content Web Parts/"

#+-This is the template name

$template = "Item_Discussion.js"

$NewItemTemplateId = $base+$template

#+-Actually Setting The Part

$wp.ItemTemplateId = $NewItemTemplateId

$wpm.SaveChanges($wp)

#+CheckIn and Publish Page

$page.CheckIn("Scripted Change")

$page.Publish("Scripted Publish")

$web.Dispose()

}

#----End Of Function----

SetWebParts.ps1 

As I said, I’m not a developer so while functional, there is probably a better way to accomplish what I’ve published here.

posted @ 11/7/2013 5:31 PM by Greg Tate

SharePoint 2013 Managed Metadata Service Application (MMSA) Gremlins

 

This post objective: To simply document something I can’t explain.

First the environment has a single WFE and two different app servers (APP1 and APP2). The SharePoint environment in question is running 2013 RTM bits (I know)…

The Timeline
  • Roughly 24 hours ago

An unplanned deployment of a custom farm solution. Mostly just an automated deployment of content pages with custom webparts. Nothing out of the ordinary here.

The standard post deploy testing revealed nothing out of the ordinary, other than Search and SSRS not playing well with each other in this production farm.

<rant>It seems the Microsoft support solution is to recreate the Search Service App, then SSRS will play nice in the logs. I’ll tell you this works but not what I’d call acceptable…</rant> 

  • 10 hours ago

Notification of Managed Metadata navigation malfunction by user

  • 6 hours ago

Start of troubleshooting MMSA. Service application interface in CA had error indicating “The Managed Metadata Service or Connection is currently not available. The Application Pool or Managed Metadata Web Service may not have been started. Please Contact your Administrator.”  Naturally I figured there was a stopped application pool, which there was, it just wasn’t one running this service.

Next I tried to open the service connection properties, only to get this error (from ULS).  Application error when access /_admin/ManageMetadataProxy.aspx, Error=Retrieving the COM class factory for component with CLSID {BDEADF26-C265-11D0-BCED-00A0C90AB50F} failed due to the following error: 800703fa Illegal operation attempted on a registry key that has been marked for deletion. (Exception from HRESULT: 0x800703FA). 

At this point I’m at a loss an figure I’ll try to restart the services via CA. Just for good measure I started the service on each server (WFE,APP1,APP2). Same results, nothing changed

Read a blog suggesting an unlikely event of the application pool need access to the service application. It worked well prior to this event, but for good measure let’s add it in.   Same results, nothing changed

  • 3 hours ago

Resigned to throw the hail marry of an IISReset, just to see if it will commit anything changed to this point. Sent notification to enterprise giving heads up of unplanned reset.

  • 2 hours ago

Getting Ready to go for lunch, figured I take a quick look before I throw the switch on the IISReset. Before checking the MMSA, I ran Get-CacheClusterHealth only to get an error “No valid cluster settings were provided with Use-CacheCluster”.  Not a big deal, anticipated this so I ran Use-CacheCluster then Get-CacheClusterHealth once more. This time I received the expected Cluster health statistics. Getting somewhat anxious to make some headway I figured I’d flip back over to the MMSA to make sure it was in fact still broken.

So yeah, as you might have guessed. It automagically started working.

  • 4 hours in the future

A cold beer or maybe…just maybe…a good shot of tequila.

Closing

In the end I can only blame the events on Gremlins, someone clearly feed the SharePoint Mogwai after dark and they had fun wreaking havoc. I can only send thanks to Rambo-Gizmo for eradicating the issue.

What I hate most of today’s event is the numerous posts such as this one by SharePointBabe coming to the solution it’s just quicker to rebuild the service application. My issue here is this is not really an acceptable solution. I wouldn’t have such an issue if Microsoft support didn’t take the same approach…but then I’ve already had my rant for this post.

posted @ 11/7/2013 3:09 PM by Greg Tate

Get list of all default values for every table

This script will list all default values for every table.

SELECT ao.name AS TABLE_NAME, dc.name AS CONSTRAINT_NAME, dc.definition AS DEFAULT_VALUE, ac.name AS COLUMN_NAME 
FROM sys.default_constraints AS dc 
INNER JOIN sys.all_columns AS ac ON dc.parent_object_id = ac.object_id 
AND dc.parent_column_id = ac.column_id 
INNER JOIN sys.all_objects AS ao ON ac.object_id = ao.object_id


posted @ 10/11/2013 1:05 PM by Chris Barba

List all indexes in a database

This script will list all indexes and their types (clustered/nonclustered).

SELECT  so.name AS TableName
            , si.name AS IndexName
            , si.type_desc AS IndexType
FROM            sys.indexes si 
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE so.type = 'U'    --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY so.name, si.type 

posted @ 10/11/2013 1:37 AM by Chris Barba

List all stored procedures in a database

This script will provide a list of all stored procedures in a database along with the create script for each.

SELECT specific_name, routine_definition 
  FROM information_schema.routines 
 WHERE routine_type = 'PROCEDURE'
 

posted @ 10/10/2013 4:03 PM by Chris Barba

List all synonyms in a database

This script will list all synonyms you have in a database.  It will tell you the name, the server connecting to, the database, schema, and object name your connecting to.

This is helpful to figure out if you have your synonyms pointing to the wrong environment.

SELECT name, 
COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName, 
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName, 
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName, 
PARSENAME(base_object_name,1) AS objectName 
FROM sys.synonyms 
ORDER BY serverName,dbName,schemaName,objectName

posted @ 10/10/2013 2:02 PM by Chris Barba

Summary of last night’s job run

Here is a script that will provide a summary of jobs that ran last night.
This is helpful if you want a summary of your jobs instead of an email from each job.

SELECT
    j.name, 
    last_run_outcome = 
    CASE 
        WHEN js.last_run_outcome = 0 THEN 'Failed' 
        WHEN js.last_run_outcome = 1 THEN 'Succeeded' 
        WHEN js.last_run_outcome = 2 THEN 'Retry' 
        WHEN js.last_run_outcome = 3 THEN 'Canceled' 
        ELSE 'Unknown' 
    END, 
    last_run_datetime = msdb.dbo.agent_datetime( 
        CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
        CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) 
FROM msdb.dbo.sysjobs j 
JOIN msdb.dbo.sysjobsteps js 
ON j.job_id = js.job_id
WHERE msdb.dbo.agent_datetime(CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
                            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) > DATEADD (dd , -1 , GETDATE())
ORDER BY Name

posted @ 10/10/2013 12:19 PM by Chris Barba

List all Primary Keys in a database

This script will list all primary keys in a database.

SELECT ST.name AS TABLE_NAME, SKC.name AS CONSTRAINT_NAME, SC.name AS CONSTRAINT_COLUMN_NAME, SC.column_id AS COLUMN_ORDER 
FROM sys.key_constraints AS SKC 
INNER JOIN sys.tables AS ST ON ST.object_id = SKC.parent_object_id 
INNER JOIN sys.index_columns AS SIC ON SIC.object_id = ST.object_id AND SIC.index_id = SKC.unique_index_id 
INNER JOIN sys.columns AS SC ON SC.object_id = ST.object_id AND SC.column_id = SIC.column_id WHERE (LEFT(ST.name, 1) = N't') AND (ST.type = N'U')

posted @ 10/9/2013 12:53 PM by Chris Barba

Get list of columns in every table

Here is a script to get a list of columns for each table.

SELECT t.name AS TABLE_NAME, c.name AS COLUMN_NAME, c.column_id AS COLUMN_ORDER, c.system_type_id AS DATA_TYPE, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity 
FROM sys.columns AS c 
INNER JOIN sys.tables AS t ON c.object_id = t.object_id

posted @ 10/4/2013 1:00 PM by Chris Barba

List all Foreign Keys in a database

Here is a script to list all foreign keys in a database.

SELECT o1.name AS TABLE_NAME, s.name AS CONSTRAINT_NAME, c1.name AS FOREIGN_KEY, o2.name AS FOREIGN_TABLE, c2.name AS FT_PRIMARY_KEY 
FROM sys.sysforeignkeys AS fk 
INNER JOIN sys.sysobjects AS o1 
ON fk.fkeyid = o1.id 
INNER JOIN sys.sysobjects AS o2 ON fk.rkeyid = o2.id 
INNER JOIN sys.syscolumns AS c1 ON c1.id = o1.id 
AND c1.colid = fk.fkey 
INNER JOIN sys.syscolumns AS c2 ON c2.id = o2.id AND c2.colid = fk.rkey 
INNER JOIN sys.sysobjects AS s ON fk.constid = s.id

posted @ 10/4/2013 12:50 PM by Chris Barba

List of all views and scripts to create

Here is a script to list all views in a database and provide the script to create them.  This helps when moving views to new environments.
SELECT a.name AS VIEW_NAME, LEFT(b.definition, 4000) AS SQL1, SUBSTRING(b.definition, 4001, 4000) AS SQL2, LEN(b.definition) AS SQL_Length 
FROM sys.sql_modules AS b 
INNER JOIN sys.views AS a ON b.object_id = a.object_id 

posted @ 10/4/2013 12:32 PM by Chris Barba

List all tables in a database

If you ever have the need to list all tables in your database, here is a statement that you can use.
I find it helpful if you need to loop through all the tables in your database or sometimes I need to provide a list to someone else.  I have used this script when moving changes between environments as a quick check to make I didn’t forget table.s

There are many different reasons you may need a list tables.

This will return just the table name. 

SELECT name AS TABLE_NAME 
FROM sys.tables WHERE (type = N'U') AND (LEFT(name, 1) = N't')
ORDER BY name

posted @ 10/4/2013 11:22 AM by Chris Barba

How to find out what triggers you have.

I try to avoid using triggers because I always forget they are there.  I have been burned more than once by triggers updating data when I didn’t know they were there.  I have also been troubleshooting someone else’s database and been bitten by triggers.
The code from this post I copied from Chris McGowan’s blog.  He wrote up a great article about trigger status.  His post has code to create a job to email you. 
I modified it to just run in SSMS and use a table variable (in case you don’t have the permissions to create a temp table).

This script runs against master and will tell you about all triggers in all databases.

Use master


-- Create table variable
DECLARE @TriggerStatus as Table
(
      DatabaseName SYSNAME,
      TableName VARCHAR(255), 
      TriggerName VARCHAR(255),
      TriggerStatus VARCHAR(8)
);
-- Insert triggers
INSERT INTO @TriggerStatus
EXEC sp_msforeachdb
'
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''reportserver'', ''reportservertempdb'')
BEGIN
USE [?]; 
SELECT  DB_NAME() AS DatabaseName,
        OBJECT_NAME(parent_id) AS TableName,
        name AS TriggerName,
        CASE is_disabled
          WHEN 0 THEN ''Enabled''
          ELSE ''Disabled''
        END AS TriggerStatus
FROM    sys.triggers WITH ( NOLOCK )
WHERE   is_ms_shipped = 0
        AND parent_class = 1;
END'


SELECT DatabaseName, TableName, TriggerName,  TriggerStatus FROM   @TriggerStatus 

posted @ 8/20/2013 5:17 PM by Chris Barba

Latest Images