Extract job titles from AD using powershell

For a given list of userids in a file called users.txt use the following powershell command in the same directory


Get-Content users.txt | foreach {Get-ADUser -Identity $PSItem -Properties mail,title | select -Property mail, title} |Export-Csv users.csv

Or use this to find new starters in the organisation...

$lastWeek = (get-date).AddDays(-31);

Get-ADUser -Filter "Description -like 'London'"  -Properties Name, Title, Manager, whenCreated, DistinguishedName, LastLogonDate | ? whenCreated  -gt $lastWeek | select Name, Title, Manager, whenCreated, DistinguishedName, LastLogonDate | fl

Note: For windows 7 desktop users you’ll need to install the active directory modules for powershell by following the installation steps here
https://www.microsoft.com/en-gb/download/details.aspx?id=7887
For all active directory attributes / propery names, see this link here
http://www.kouti.com/tables/userattributes.htm
Another way to have done it would have been usng SQL via a linked server
https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/
e.g.
SELECT     *
FROM OPENQUERY( ADSI,
    'SELECT samaccountname, mail, title, sn
     FROM ''LDAP://dc=companyname,dc=com''
     WHERE objectCategory = ''Person'' AND objectClass= ''user''
     AND userprincipalname = ''*'' AND mail = ''*'' AND SN = ''*''
         ORDER BY useraccountcontrol
      ')
      Where
      samaccountname= ‘myaccountloginname’

SQL Management Studio short cuts

Hi guys

Not sure how many of you already knew this, but stumbled across a couple of SQL management studio shortcuts today that I was previously unaware of, and hit quite by accident.

Subsequently discovered that you can pre-configure your own as well, in  Tools, Options,  Keyboard….

More here

https://msdn.microsoft.com/en-us/library/ms174205.aspx

How to restore orphaned users in TSQL after database restore

Use the following script

 

USE [master]

SET NOCOUNT ON;

-- Create a temp table to hold orphaned accounts

if OBJECT_ID('tempdb..#Orphans') is not NULL DROP TABLE #Orphans

CREATE TABLE #Orphans (

      ID                INT NOT NULL IDENTITY,

      DBName            NVARCHAR(MAX) DEFAULT NULL,

      Username    NVARCHAR(MAX),

      userSID           NVARCHAR(MAX)

)

-- For each database get orphaned accounts

EXEC sp_MSforeachdb 'use [?]; insert into #Orphans (Username,UserSID) EXEC sp_change_users_login ''REPORT''; update #Orphans SET dbname = ''?'' where dbname is NULL'

-- Remove DBs that are not ONLINE or that are READ_ONLY as we can't alter them.

DELETE FROM #Orphans WHERE NOT [DBName] IN

(

SELECT name

FROM sys.databases

WHERE state_desc = 'ONLINE' AND is_read_only = 0 -- Databases that are ONLINE and NOT Read_Only

)

-- Loop through all remaining orphans and attempt to un-orphan them

DECLARE @CurRow   INT = 1

DECLARE @EndRow INT = (SELECT MAX(ID) FROM #Orphans)

DECLARE @SQLCmd   NVARCHAR(MAX)

WHILE @CurRow <= @EndRow

BEGIN

SET @SQLCmd = (SELECT 'USE [' + DBName + ']; EXEC sp_change_users_login ''UPDATE_ONE'', ''' + Username + ''', ''' + username + ''';' FROM #Orphans WHERE ID = @CurRow)

SET @CurRow = @CurRow + 1

PRINT @SQLCmd

EXECUTE (@SQLCmd) -- re associate the login with the user account

END

-- Drop temp table holding orphaned accounts

if OBJECT_ID('tempdb..#Orphans') is not NULL DROP TABLE #Orphans

How to run a SQL Query across multiple databases with one query

In SQL Server management studio, using, View, Registered Servers (Ctrl+Alt+G) set up the servers that you want to execute the same query across all servers for, right click the group, select new query. Then when you execute the query, the results will come back with the first column showing you the database instance that that row came from.

How to query Active Directory from SQL server

SET UP a Linked server with name ADSI to make this work (shown below)

Also further on you'll see the use of the userAccountControl field, which is a flags field that stores user account details. The list of which flags are which can be found here...

http://msdn.microsoft.com/en-us/library/windows/desktop/ms680832(v=vs.85).aspx

 

use [utilities]
SET NOCOUNT ON;

-- Ensure database link to AD called ADSI exists...

IF object_id('[Utilities].[dbo].[AccountSyncCheck]') IS NOT NULL DROP TABLE [dbo].[AccountSyncCheck]
CREATE TABLE [dbo].[AccountSyncCheck](
      [LOGINID] [varchar](100) collate database_default NOT NULL,
      [EMAILADDRESS] [varchar](255) collate database_default NULL,
      [EMPLOYEEID] [varchar](10) collate database_default NULL,
      [FIRSTNAME] [varchar](255) collate database_default NULL,
      [LASTNAME] [varchar](255) collate database_default NULL,
      [DEPARTMENT] [varchar](255) collate database_default NULL,
      [OFFICENAME] [varchar](255) collate database_default NULL,
      [DISPLAYNAME] [varchar](255) collate database_default NULL,
      userAccountControl      int,
CONSTRAINT [PK_AccountSyncCheck_LOGINID] PRIMARY KEY CLUSTERED
(
      [LOGINID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

IF object_id('tempdb..#temp') IS NOT NULL  DROP TABLE #temp;
CREATE TABLE #Temp
(
    samaccountname             VARCHAR (100) ,
    givenname                  VARCHAR (255)  NULL,
    sn                         VARCHAR (255)  NULL,
    mail                       VARCHAR (255)  NULL,
    department                 VARCHAR (255)  NULL,
    physicalDeliveryOfficeName NVARCHAR (255) NULL,
    employeeid                 VARCHAR (15)   NULL,
    displayname                VARCHAR (255)  NULL,
    userAccountControl              int null
);

DECLARE @sql      AS VARCHAR (MAX)
DECLARE @i        AS INT = ASCII('A');

WHILE @i <= ASCII('Z')
    BEGIN
        SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                                    FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                                          FROM ''''LDAP://dc=mydomain,dc=com'''' 
                            WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                            AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                            AND         samaccountname >= '''' ' + char(@i) + '  ''''  and  samaccountname < '''' ' + char(@i + 1) + ' ''''  
                            '') ';
PRINT @SQL
        INSERT INTO #Temp
        EXECUTE (@sql);
        SET @i = @i + 1;
    END

-- deal with oddball characters
SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname ,userAccountControl
                        FROM ''''LDAP://dc=mydomain,dc=com'''' 
                        WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                        AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                        AND         samaccountname < ''''A''''
                        '') ';

INSERT INTO #Temp
EXECUTE (@sql);

SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM ''''LDAP://dc=mydomain,dc=com'''' 
                        WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                        AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                        AND         samaccountname >= ''''ZZZZZZZ''''
                        '') ';

INSERT INTO #Temp
EXECUTE (@sql);

IF (SELECT COUNT(*)
    FROM   #Temp) > 0
    BEGIN
        truncate table AccountSyncCheck
        INSERT INTO AccountSyncCheck (LOGINID,EMAILADDRESS,EMPLOYEEID,FIRSTNAME,LASTNAME,DEPARTMENT,OFFICENAME,DISPLAYNAME, userAccountControl)
        SELECT   samaccountname,
                 mail,
                 LEFT(employeeid, 10),
                 givenname,
                 sn,
                 department,
                 physicalDeliveryOfficeName,
                 displayname,
                 userAccountControl
        FROM     #Temp
        ORDER BY samaccountname;
    END

SET NOCOUNT OFF;

--select top 10 * from beacon.dbo.rraperson
select *
from utilities.dbo.AccountSyncCheck

/*
SELECT     
'active RRA person(s) with invalid email address' AS     alert,
iv.PersonID, InternetAddress, Preferred, OfficeID, Loginname, Active, Responsibility, RetiredDate
from beacon.dbo.IV_PersonInternetAddressCompletePreferred iv
inner join beacon.dbo.rraperson r on r.personid = iv.personid
where ISNULL(iv.internetaddress, '') <> ''
--and iv.internetaddress not like '%@mydomain.com%' and iv.internetaddress not like '%@russreyn.com%'
and active = 1
and iv.personid not in (10000165,40002053,900008913,40001263,40001285)
*/

-- Active in Beacon disabled in AD
select *
from utilities.dbo.AccountSyncCheck

-- find active where they should be inactive
select *
from AccountSyncCheck b
inner join beacon.dbo.rrapersonsummary r on r.loginname = 'mydomain\' + b.loginid 
where r.Active = 1
and useraccountcontrol & 2 = 2

-- find beacon spelling mistakes in email addresses
select b.loginid, pcp.PersonID, ActiveDirectoryEmailAddress = b.emailaddress, BeaconEmailAddress = pcp.InternetAddress
from AccountSyncCheck b
inner join beacon.dbo.rrapersonsummary r on r.loginname = 'mydonamin\' + b.loginid 
inner join Beacon.dbo.IV_PersonInternetAddressCompletePreferred pcp on pcp.PersonID = r.id
where pcp.InternetAddress <> b.emailaddress
and r.Active = 1
and useraccountcontrol & 2 = 0

SQL Server version checking

SELECT @@version

then compare your result against this list

http://sqlserverbuilds.blogspot.co.uk/

P.S. Pagination in a sub report works only in SQL 2008 R2 versions and above, but only if there is only one column in the Tablix of the master report.

SQL Server Report Server Investigations

The following SQL will let you find the worst performing reports, and also the ones that error on your reporting services database instance.

SELECT C.Path,
    C.Name

          , E.Requesttype
           --WHEN 1 THEN 'Subscription'
           --WHEN 0 THEN 'Report Launch'
           --ELSE ''
           --END
          ,E.TimeStart
          ,E.TimeProcessing
          ,E.TimeRendering
          ,E.TimeEnd
          ,[TimeTotal (ms)] = DATEDIFF ( millisecond , E.TimeStart , E.TimeEnd )
          ,E.Status
          ,E.InstanceName
          ,E.UserName
     FROM Reportserver.dbo.ExecutionLog E (NOLOCK)
     INNER JOIN Reportserver.dbo.Catalog C (NOLOCK)
       ON E.ReportID = C.ItemID
WHERE 1=1
--    WHERE C.Name = 'Project Full Control Comments'
and E.Requesttype = 0
and E.Status <> 'rsSuccess'

ORDER BY [TimeTotal (ms)] DESC

Logical fragmentation in SQL Server Indexes

My colleague Lawrence today discovered that a query we had that was taking over 2hrs to run (and still timing out), was due to a non clustered index being badly fragmented on the table in question.

To discover this fact he used the following queries, before rebuilding all indexes on the table, which then fixed the issue, and got the query coming back in 2 seconds.

--1. find index names
sp_helpindex 'dbo.MyTableName'

--2. show index logical fragmentation. Scan density should be above 97%
dbcc showcontig (MyTableName) WITH TABLERESULTS, ALL_INDEXES

--3. show when indexes were last rebuilt
Declare @dbid int
Select @dbid = db_id('Beacon')
Select objectname=object_name(i.object_id)
, indexname=i.name, i.index_id
, o.create_date, o.modify_date
from sys.indexes i, sys.objects o
where objectproperty(o.object_id,'IsUserTable') = 1
--and i.index_id NOT IN
--(select s.index_id
--from sys.dm_db_index_usage_stats s
--where s.object_id=i.object_id and
--i.index_id=s.index_id and
--database_id = @dbid )
and o.object_id = i.object_id
and object_name(i.object_id)= 'MyTableName'
order by o.modify_date desc

Configuring SQL Server Service Broker

Found a good step by step article here on SQL server central

http://www.sqlservercentral.com/articles/Service+Broker/67513/

At a high level these are the points to remember

To configure the service broker to send and receive messages between databases (either in local/remote instances), it needs few components in place to start the service. These are listed below.

1. Enable the Service Broker on the database
2. Create valid Message Types.
3. Create a Contract for the conversation.
4. Create Queues for the Communication.
5. Create Services for the Communication.

SQL to drop and recreate foreign keys

SELECT  'To'                                                      'Direction',
            OBJECT_NAME(sfk.constraint_object_id)     'ConstraintName',
        OBJECT_NAME(parent_object_id)                 'ConstrainedTable',
        psc.name                                            'ConstrainedColumn',
        OBJECT_NAME(referenced_object_id)       'ConstrainingTable',
        csc.name                                            'ConstraingColumn',
        'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' WITH NOCHECK ADD  CONSTRAINT ' + QUOTENAME(OBJECT_NAME(sfk.constraint_object_id)) +  ' FOREIGN KEY(['+psc.name + '])  REFERENCES ' + OBJECT_NAME(referenced_object_id) +' ([' + csc.name +'])' AS [CreateScript] ,
        'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(sfk.constraint_object_id)) AS [DropScript]
FROM    sys.foreign_key_columns sfk
        INNER JOIN sys.columns psc ON psc.column_id = sfk.parent_column_id
                                      AND sfk.parent_object_id = psc.object_id
        INNER JOIN sys.columns csc ON csc.column_id = sfk.referenced_column_id
                                      AND sfk.referenced_object_id = csc.object_id
WHERE   OBJECT_NAME(sfk.constraint_object_id) IN

(
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
)

TSQL Performance Tuning

 

Below is an example of the TSQL you can use to investigate your stored procedure's performance. Best to put this in a snippet if you're using VS 2012 or greater versions of SQL Management Studio I find.

SET NOCOUNT ON

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

CHECKPOINT

GO

PRINT ' '

PRINT '-- START: ProcName --------------------------------------------------------------------------------------------------------------------------------'

PRINT ' '

SET STATISTICS IO ON

SET STATISTICS TIME ON

EXEC dbo.MyProcNameHere

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT ' '

PRINT '-- END: ProcName --------------------------------------------------------------------------------------------------------------------------------'

PRINT ' '

GO

Handy SQL for extracting creating your test data insert scripts

 

SET NOCOUNT ON

GO

PRINT 'Checking for the existence of this procedure'

IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists

BEGIN

PRINT 'Procedure already exists. So, dropping it'

DROP PROC sp_generate_inserts

END

GO

CREATE PROC sp_generate_inserts

(

      @table_name varchar(776), -- The table/view for which the INSERT statements will be generated using the existing data

      @target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted

      @include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement

      @from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)

      @include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement

      @debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination

      @owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table

      @ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns

      @ommit_identity bit = 0, -- Use this parameter to ommit the identity columns

      @top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows

      @cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement

      @cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement

      @disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements

      @ommit_computed_cols bit = 0        -- When 1, computed columns will not be included in the INSERT statement

)

AS

BEGIN

/***********************************************************************************************************

Procedure:  sp_generate_inserts  (Build 22)

            (Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.)

Purpose:    To generate INSERT statements from existing data.

            These INSERTS can be executed to regenerate the data at some other location.

            This procedure is also useful to create a database setup, where in you can

            script your data along with your table definitions.

Written by: Narayana Vyas Kondreddi

http://vyaskn.tripod.com

Acknowledgements:

            Divya Kalra -- For beta testing

            Mark Charsley     -- For reporting a problem with scripting uniqueidentifier columns with NULL values

            Artur Zeygman     -- For helping me simplify a bit of code for handling non-dbo owned tables

            Joris Laperre   -- For reporting a regression bug in handling text/ntext columns

Tested on: SQL Server 7.0 and SQL Server 2000 and SQL Server 2005

Date created:     January 17th 2001 21:52 GMT

Date modified:    May 1st 2002 19:50 GMT

Email:            vyaskn@hotmail.com

NOTE:       This procedure may not work with tables with too many columns.

            Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types

            Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results

            IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed

            you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts

            like nchar and nvarchar

            ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON

Example 1:  To generate INSERT statements for table 'titles':

            EXEC sp_generate_inserts 'titles'

Example 2: To ommit the column list in the INSERT statement: (Column list is included by default)

            IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,

            to avoid erroneous results

            EXEC sp_generate_inserts 'titles', @include_column_list = 0

Example 3:  To generate INSERT statements for 'titlesCopy' table from 'titles' table:

            EXEC sp_generate_inserts 'titles', 'titlesCopy'

Example 4:  To generate INSERT statements for 'titles' table for only those titles

            which contain the word 'Computer' in them:

            NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter

            EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"

Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:

            (By default TIMESTAMP column's data is not scripted)

            EXEC sp_generate_inserts 'titles', @include_timestamp = 1

Example 6:  To print the debug information:

            EXEC sp_generate_inserts 'titles', @debug_mode = 1

Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name

            To use this option, you must have SELECT permissions on that table

            EXEC sp_generate_inserts Nickstable, @owner = 'Nick'

Example 8: To generate INSERT statements for the rest of the columns excluding images

            When using this otion, DO NOT set @include_column_list parameter to 0.

            EXEC sp_generate_inserts imgtable, @ommit_images = 1

Example 9: To generate INSERT statements excluding (ommiting) IDENTITY columns:

            (By default IDENTITY columns are included in the INSERT statement)

            EXEC sp_generate_inserts mytable, @ommit_identity = 1

Example 10:       To generate INSERT statements for the TOP 10 rows in the table:

            EXEC sp_generate_inserts mytable, @top = 10

Example 11:       To generate INSERT statements with only those columns you want:

            EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"

Example 12:       To generate INSERT statements by omitting certain columns:

            EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"

Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:

            EXEC sp_generate_inserts titles, @disable_constraints = 1

Example 14:       To exclude computed columns from the INSERT statement:

            EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1

***********************************************************************************************************/

SET NOCOUNT ON

--Making sure user only uses either @cols_to_include or @cols_to_exclude

IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))

BEGIN

RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)

RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified

END

--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format

IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))

BEGIN

RAISERROR('Invalid use of @cols_to_include property',16,1)

PRINT 'Specify column names surrounded by single quotes and separated by commas'

PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'

RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property

END

IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))

BEGIN

RAISERROR('Invalid use of @cols_to_exclude property',16,1)

PRINT 'Specify column names surrounded by single quotes and separated by commas'

PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'

RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property

END

--Checking to see if the database name is specified along wih the table name

--Your database context should be local to the table for which you want to generate INSERT statements

--specifying the database name is not allowed

IF (PARSENAME(@table_name,3)) IS NOT NULL

BEGIN

RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)

RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed

END

--Checking for the existence of 'user table' or 'view'

--This procedure is not written to work on system tables

--To script the data in system tables, just create a view on the system tables and script the view instead

IF @owner IS NULL

BEGIN

IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))

BEGIN

RAISERROR('User table or view not found.',16,1)

PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'

PRINT 'Make sure you have SELECT permission on that table or view.'

RETURN -1 --Failure. Reason: There is no user table or view with this name

END

END

ELSE

BEGIN

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)

BEGIN

RAISERROR('User table or view not found.',16,1)

PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'

PRINT 'Make sure you have SELECT permission on that table or view.'

RETURN -1 --Failure. Reason: There is no user table or view with this name       

END

END

--Variable declarations

DECLARE           @Column_ID int,

            @Column_List varchar(8000),

            @Column_Name varchar(128),

            @Start_Insert varchar(786),

            @Data_Type varchar(128),

            @Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements

            @IDN varchar(128) --Will contain the IDENTITY column's name in the table

--Variable Initialization

SET @IDN = ''

SET @Column_ID = 0

SET @Column_Name = ''

SET @Column_List = ''

SET @Actual_Values = ''

IF @owner IS NULL

BEGIN

SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'

END

ELSE

BEGIN

SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'

END

--To get the first column's ID

SELECT      @Column_ID = MIN(ORDINAL_POSITION)

FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)

WHERE       TABLE_NAME = @table_name AND

(@owner IS NULL OR TABLE_SCHEMA = @owner)

--Loop through all the columns of the table, to get the column names and their data types

WHILE @Column_ID IS NOT NULL

BEGIN

SELECT      @Column_Name = QUOTENAME(COLUMN_NAME),

            @Data_Type = DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)

WHERE       ORDINAL_POSITION = @Column_ID AND

            TABLE_NAME = @table_name AND

(@owner IS NULL OR TABLE_SCHEMA = @owner)

IF @cols_to_include IS NOT NULL --Selecting only user specified columns

BEGIN

IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0

BEGIN

GOTO SKIP_LOOP

END

END

IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns

BEGIN

IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0

BEGIN

GOTO SKIP_LOOP

END

END

--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column

IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1

BEGIN

IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column

SET @IDN = @Column_Name

ELSE

GOTO SKIP_LOOP               

END

--Making sure whether to output computed columns or not

IF @ommit_computed_cols = 1

BEGIN

IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1

BEGIN

GOTO SKIP_LOOP                           

END

END

--Tables with columns of IMAGE data type are not supported for obvious reasons

IF(@Data_Type in ('image'))

BEGIN

IF (@ommit_images = 0)

BEGIN

RAISERROR('Tables with image columns are not supported.',16,1)

PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'

PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'

RETURN -1 --Failure. Reason: There is a column with image data type

END

ELSE

BEGIN

GOTO SKIP_LOOP

END

END

--Determining the data type of the column and depending on the data type, the VALUES part of

--the INSERT statement is generated. Care is taken to handle columns with NULL values. Also

--making sure, not to lose any data from flot, real, money, smallmomey, datetime columns

SET @Actual_Values = @Actual_Values  +

CASE

WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')

THEN

'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'

WHEN @Data_Type IN ('datetime','smalldatetime')

THEN

'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'

WHEN @Data_Type IN ('uniqueidentifier')

THEN

'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'

WHEN @Data_Type IN ('text','ntext')

THEN

'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'

WHEN @Data_Type IN ('binary','varbinary')

THEN

'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'

WHEN @Data_Type IN ('timestamp','rowversion')

THEN

CASE

WHEN @include_timestamp = 0

THEN

'''DEFAULT'''

ELSE

'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'

END

WHEN @Data_Type IN ('float','real','money','smallmoney')

THEN

'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ',2)' + ')),''NULL'')'

ELSE

'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ')' + ')),''NULL'')'

END + '+' + ''',''' + ' + '

--Generating the column list for the INSERT statement

SET @Column_List = @Column_List +  @Column_Name + ','

SKIP_LOOP: --The label used in GOTO

SELECT      @Column_ID = MIN(ORDINAL_POSITION)

FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)

WHERE       TABLE_NAME = @table_name AND

            ORDINAL_POSITION > @Column_ID AND

(@owner IS NULL OR TABLE_SCHEMA = @owner)

--Loop ends here!

END

--To get rid of the extra characters that got concatenated during the last run through the loop

SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)

SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)

IF LTRIM(@Column_List) = ''

BEGIN

RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)

RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter

END

--Forming the final string that will be executed, to output the INSERT statements

IF (@include_column_list <> 0)

BEGIN

SET @Actual_Values =

'SELECT ' +

CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +

'''' + RTRIM(@Start_Insert) +

' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' +

' +''VALUES(''+ ' +  @Actual_Values  + '+'')''' + ' ' +

COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')

END

ELSE IF (@include_column_list = 0)

BEGIN

SET @Actual_Values =

'SELECT ' +

CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +

'''' + RTRIM(@Start_Insert) +

' '' +''VALUES(''+ ' +  @Actual_Values + '+'')''' + ' ' +

COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')

END

--Determining whether to ouput any debug information

IF @debug_mode =1

BEGIN

PRINT '/*****START OF DEBUG INFORMATION*****'

PRINT 'Beginning of the INSERT statement:'

PRINT @Start_Insert

PRINT ''

PRINT 'The column list:'

PRINT @Column_List

PRINT ''

PRINT 'The SELECT statement executed to generate the INSERTs'

PRINT @Actual_Values

PRINT ''

PRINT '*****END OF DEBUG INFORMATION*****/'

PRINT ''

END

PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'

PRINT '--Build number: 22'

PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'

PRINT '--http://vyaskn.tripod.com'

PRINT ''

PRINT 'SET NOCOUNT ON'

PRINT ''

--Determining whether to print IDENTITY_INSERT or not

IF (@IDN <> '')

BEGIN

PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'

PRINT 'GO'

PRINT ''

END

IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)

BEGIN

IF @owner IS NULL

BEGIN

SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'

END

ELSE

BEGIN

SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'

END

PRINT 'GO'

END

PRINT ''

PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''

--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!

EXEC (@Actual_Values)

PRINT 'PRINT ''Done'''

PRINT ''

IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)

BEGIN

IF @owner IS NULL

BEGIN

SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'

END

ELSE

BEGIN

SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'

END

PRINT 'GO'

END

PRINT ''

IF (@IDN <> '')

BEGIN

PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'

PRINT 'GO'

END

PRINT 'SET NOCOUNT OFF'

SET NOCOUNT OFF

RETURN 0 --Success. We are done!

END

GO

PRINT 'Created the procedure'

GO

--Mark procedure as system object

EXEC sys.sp_MS_marksystemobject sp_generate_inserts

GO

PRINT 'Granting EXECUTE permission on sp_generate_inserts to all users'

GRANT EXEC ON sp_generate_inserts TO public

SET NOCOUNT OFF

GO

PRINT 'Done'

How to change the default collation of SQL 2008 R2 database

I've managed to change mine with this following statement. It only took about 4 mins to fix, once I'd got the right installer files mounted on my V drive, and shut down my virus scanner.

Ran this from a VS 2008 .NET command prompt, after shutting down all my sql server services too…

V:\>setup /Q /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD=NotTellingYou! /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Latin1_General_CI_AI

How to query the TFS 2010 Source control database (TFSWarehouse) using TSQL

Found this blog useful today, when a colleague of mine asked how he can produce a report of all the changes from members of his team on their database scripts in TFS source control...

http://stackoverflow.com/questions/834107/tfs-query-in-visual-studio-to-get-all-check-ins

Something like this example was what we ended up using ...

USE [Tfs_DefaultCollection]  

SELECT distinct cs.CreationDate, cs.[ChangeSetId], c.DisplayPart, cs.[Comment] , v.[FullPath]
from [tbl_ChangeSet] AS cs
left outer JOIN [tbl_Identity] AS i ON cs.[OwnerId] = i.[IdentityId]
left outer JOIN [Constants] AS c ON i.[TeamFoundationId] = c.[TeamFoundationId]
left outer join dbo.tbl_Version as v on v.Versionfrom = cs.ChangeSetId
WHERE creationdate > '04/12/2012'
and (v.FullPath like '%\Database%')
ORDER BY cs.[CreationDate] desc

Altering an indexed view drops the indexes

Whilst altering the an indexed view yesterday I noticed some quirky behaviour that I was not expecting until a colleague of mine kindly pointed it out to me.

If you alter an indexed view, then all the indexes for that view will be dropped too! (This is not pointed out to you in any of the feedback messaging that SQL server outputs either when altering the view).

So you'll have to put back the indexes, with the clustered index being created again first, before you can use the NOEXPAND hint in any dependant database object.

Problem with reseeding IDENTITY column in a SQL Server 2008 database table

Problem that we've just discovered with DBCC CHECKIDENT ('MyTable', reseed, 1) which doesn't reset the identity seed to 1 if the table has had data in it before, instead the newly inserted rows will start from ID = 2.

Example below…

begin tran

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MerrickTest]') AND type in (N'U'))

DROP TABLE [dbo].[MerrickTest]

CREATE TABLE [dbo].[MerrickTest](

            [ID] [int] IDENTITY(1,1) NOT NULL,

            [Value] [varchar](50) NULL,

CONSTRAINT [PK_MerrickTest] PRIMARY KEY CLUSTERED

(

            [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SELECT IDENT_SEED('dbo.MerrickTest') AS Identity_Seed;

insert into merricktest (value) values ( 'test' )

select * from merricktest

delete from MerrickTest

SELECT IDENT_SEED('dbo.MerrickTest') AS Identity_Seed;

DBCC CHECKIDENT (merricktest, reseed, 1)

SELECT IDENT_SEED('dbo.MerrickTest') AS Identity_Seed;

insert into merricktest (value) values ( 'test' )

select * from merricktest -- note that the value in the ID column is now 2 not 1

drop table MerrickTest

rollback

Solution...

One solution you could use for this is to check the last update on the database table, and then reseed from zero if it has been updated in the past using the following sql

Select object_name(object_id), last_user_update from sys.dm_db_index_usage_stats where object_name(object_id) like 'MerrickTest'

Cannot create database diagram

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/9a9bf26c-bb14-4353-a4ec-ab53d68d6afc

Got this today on SQL 2008 R2. Work around was this one...

Right click database in  management studio then click properties.
select "files" page
then set owner as "sa"

• Proposed As Answer by Jerome2606 Wednesday, January 11, 2012 11:46 AM

How to concatenate multiple rows into one row in TSQL

The following query will achieve this quite nicely.

SELECT
STUFF( (SELECT TOP 10 
            '; ' + COALESCE (Surname + ',' + Forename + ' ' + MiddleInitial ,  Surname + ',' + Forename, Surname)
            FROM Person
            FOR XML PATH('')),
1,
2,
'')

The important pieces of this query are that we're using the STUFF function to remove the starting '; ' from the resulting string, and also using FOR XML PATH('') to get the results of the inner SQL select into a single row result set instead of 10 rows.

Results come back in the format...

Surname1, Forename1; Surname2, Forename2; Surname3, Forename3

Using NHibernate to format your SQL

Just did this in the immediate window from break point set in Abstract Batcher, GetSQL method in the NHibernate source code

sql.ToString()

"exec [dbo].[GetProjectComments] ?"

SqlClientDriver driver= new SqlClientDriver();

SqlStringFormatter formatter = new SqlStringFormatter(driver);

sql.Visit(formatter)

Expression has been evaluated and has no value

formatter.GetFormattedText()

"exec [dbo].[GetProjectComments] @p0"

Thinking of using this to write a custom little app on the side to make all my SQL look nice again. I know SQL Prompt already does this nicely, but don't have a license for that right now, so this is a nice free alternative I guess.

Who dropped my SQL Database?

Today we found that one of our databases had mistakenly been dropped from our SQL server. To find out who you can use one of these two queries which look at the SQL log file trace...

DECLARE @path varchar(256)

SELECT @path = path
FROM sys.traces
where id = 1

SELECT *
FROM fn_trace_gettable(@path, 1)
where databasename = 'myDbName'
order by starttime desc

To prove it without a shadow of a doubt, you can use this more detailed query...

declare @databaseID int = 5 -- TODO: Figure out your database id here

;With cteObjectTypes AS
      (
      SELECT
            TSV.trace_event_id,
            TSV.subclass_name,
            TSV.subclass_value
      FROM
            sys.trace_subclass_values AS TSV JOIN
            sys.trace_columns AS TC ON
                  TSV.trace_column_id = TC.trace_column_id
      WHERE
            TC.[name] = 'ObjectType'
      ),
      cteEventSubClasses AS
      (
      SELECT
            TSV.trace_event_id,
            TSV.subclass_name,
            TSV.subclass_value
      FROM
            sys.trace_subclass_values AS TSV JOIN
            sys.trace_columns AS TC ON
                  TSV.trace_column_id = TC.trace_column_id
      WHERE
            TC.[name] = 'EventSubClass'
      )
SELECT
    TE.[name],
    I.ApplicationName,
      I.BigintData1,
      I.ClientProcessID,
      I.ColumnPermissions,
      I.DatabaseID,
      I.DatabaseName,
      I.DBUserName,
      I.Duration,
      I.EndTime,
      I.Error,
      I.EventSequence,
      Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
      I.FileName,
      I.HostName,
      I.IndexID,
      I.IntegerData,
      I.IsSystem,
      I.LineNumber,
      I.LoginName,
      I.LoginSid,
      I.NestLevel,
      I.NTDomainName,
      I.NTUserName,
      I.ObjectID,
      I.ObjectID2,
      I.ObjectName,
      Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
      I.OwnerName,
      I.ParentName,
      I.Permissions,
      I.RequestID,
      I.RoleName,
      I.ServerName,
      I.SessionLoginName,
      I.Severity,
      I.SPID,
      I.StartTime,
      I.State,
      I.Success,
      I.TargetLoginName,
      I.TargetLoginSid,
      I.TargetUserName,
      I.TextData,
      I.TransactionID,
      I.Type,
      I.XactSequence
FROM
    sys.traces T CROSS Apply
    sys.fn_trace_gettable(T.[path], T.max_files) I JOIN
    sys.trace_events AS TE ON
            I.EventClass = TE.trace_event_id LEFT JOIN
      cteEventSubClasses AS ESC ON
            TE.trace_event_id = ESC.trace_event_id And
            I.EventSubClass = ESC.subclass_value LEFT JOIN
      cteObjectTypes AS OT ON
            TE.trace_event_id = OT.trace_event_id AND
            I.ObjectType = OT.subclass_value
WHERE
    T.is_default = 1
    --TE.NAME = 'Object:Deleted'
    --and databaseid = @databaseID
    order by starttime desc

Safe to say with the latter query the evidence was pretty damning for one individual on our team