Blog ala Vidar

SQL, AppFrame and other cool technologies

Tag Archives: SQL

SQL Updates

It’s been way too long since my last blog post, so I figured it was about time to give a little SQL update on things I’ve learned the last months.

After attending NDC, watching Peter Myers’ sessions about SQL Server Analysis Services and data mining, I’ve played A LOT with SSAS. It’s way different compared to T-SQL, but it’s a lot of fun and a nice challenge. Soon got a cube working as I want it, to show off to the department managers at the company I work at. Hopefully I’ve done a good enough job to encourage them to use it, and maybe even recommend to customers. We haven’t used SSAS too much yet, but that might be about to change.

I’ve also updated my scripts library to include “FINDSpace Usage By Table”. You can use this if you’ve done big clean-ups in your databases, and what to “shrink” some tables. “RECREATEForeign Keys” – I wrote this one to make automatic data-transfer easier. You run the first part to store the foreign keys in a temporary table, then remove them. Then you can do all your data-transfer-stuff without any issues. Then just run the rest of the script to create the foreign keys again.

NB!!! These are of course scripts without any warranties. I’m sharing them because you might need something similar, but always look over scripts you find on the internet. Not just mine, but everyone else’s too!

Last but not least, I’ve played some with SQL Server 2011 CTP3, codenamed Denali. The first thing you notice after installing it, if you haven’t already tested CTP1 (CTP2 was not public), is the new integration with Visual Studio 2010. Pretty! Not a big difference from Management Studio, but everything is now in VS; BI tools and Management Studio. I’ve already written about some new exciting features in Denali, but here’s some I didn’t mention, or didn’t know of at the time:

  • A new edition/version called Express Local DB Runtime. Zero-configuration but full support for all programmability features.
  • Books online has changed. It was awful in CTP1, but I think I like the version in CTP3.
  • In-Memory Column-store indexes aka Apollo – Read more about it here.
  • New restore options. Now you got a timeline to choose from!
  • A couple of new functions, Analytic, Conversion, THROW. Full list of new functions.
  • You’re now able to format the result set from EXECUTE. For example returning XML.

Googling “SQL Server Denali CTP 3 new features” will give you a lot more features.

Advertisements

COLUMNS_UPDATED

From SQL Server 2000, there’s been support for a function called COLUMNS_UPDATED. I haven’t used it much, but I’ve written triggers using it a couple of times, and every time I do that, I’ve forgot how it works. Now, I’m going to put it here on my blog 🙂

So, basically, this function returns which fields are actually updated. This can be useful if for instance you want users to be able to update one column, but not the others on a table. Instead of doing:

IF UPDATE(AllowedField) 
	AND NOT UPDATE(NotAllowedField1) 
	AND NOT UPDATE(NotAllowedField2) 
	AND NOT UPDATE(NotAllowedField3)
BEGIN
	RAISERROR('You are not allowed to change fields other than: AllowedField', 18, 1)
	ROLLBACK TRANSACTION
	RETURN
END

The problem with this solution is of course if you add new fields, you’re more or less proper-fucked.

CREATE TABLE test (
	NotAllowedField1 NVARCHAR(128),
	NotAllowedField2 NVARCHAR(128),
	NotAllowedField3 NVARCHAR(128),
	AllowedField NVARCHAR(128)
)
GO

INSERT INTO test
  (NotAllowedField1, NotAllowedField2, NotAllowedField3, AllowedField) 
VALUES
  ('aaaa', 'aaaa', 'aaaa', 'aaaa')
GO 5

CREATE TRIGGER test_ITrig ON test 
AFTER UPDATE 
AS
BEGIN
	DECLARE @AllowedFieldName AS NVARCHAR(128)
	DECLARE @ColumnNumber AS INT

	SET @AllowedFieldName = 'AllowedField'
	SET @ColumnNumber = (SELECT colid 
						FROM sys.syscolumns 
						WHERE id = OBJECT_ID('test') 
						  AND name = @AllowedFieldName)

	IF (COLUMNS_UPDATED() ^ POWER(2, ((@ColumnNumber) - 1))) > 0
	BEGIN
		RAISERROR('You are not allowed to change fields other than: %s', 
				18, 1, @AllowedFieldName)
		ROLLBACK TRANSACTION
		RETURN
	END
END
GO


-- Not allowed single
UPDATE test
	SET NotAllowedField1 = 'bbbb'

-- Not allowed multiple
UPDATE test
	SET
	  AllowedField = 'bbbb',
	  NotAllowedField2 = 'bbbb',
	  NotAllowedField3 = 'bbbb'

-- Allowed single
UPDATE test
	SET AllowedField = 'bbbb'


-- Clean-up
DROP TRIGGER test_ITrig
GO
DROP TABLE test
GO

5 minutes interview #29 – Aleksandr Narožnij

First time I met Aleksandr was my first visit to Klaipeda, Lithuania. He was kind enough to pick me up at the airport in Palanga (about 45 minutes by car), even though it was late at night. Before I met him, he had been on my team at Omega AS, and from day 1 I knew we’d be good friends. Aleksandr has been my guide every time I’ve been in Lithuania, taking me EVERYWHERE, helping me buy boxers (yes, believe it or not, this is REALLY hard to find when you’re out of clean ones) and generally just being awesome! Aleksandr joined Omega in 2006 as a trainee while finishing his studies. He finished these 1,5 years ago, and has been working with PIMS in the Products-team since then.

What did you study?
I’ve been studying Computer Science at Klaipeda University for six years. I have defended the thesis “Efficiency of database table’s indexes” for my bachelor and “Implementation of decision making process’ rules model in a software system based on models transformation” for the master degree. I got excellent grades on both – true story 🙂
Studies were mainly held at the faculty of Natural Science and Mathematics; so you can imagine how many additional “important” subjects did we have: ecology, math of all various types and even social behavior deviation 🙂 Of course, I don’t remember much of what I’ve been taught, but I’m grateful to all my tutors for helping me master the main skill – skill of studying on my own.

What’s the biggest difference between being a trainee and a full-time employee in Omega UAB?
Trainee position in UAB is a dream job. After passing the standard Certification Program you are normally given some real projects to work with, however you are free to use part of your working time for exploring how things work. I came to Omega with some basic theoretical knowledge and learnt pretty much everything in practice. Many thanks to Vidar – he spent much time and effort with us trainees.
I still “google” a lot, but now I don’t have enough time to go deep into details – that’s the main difference.

At work, what’s in and what’s out of your comfort zone?
I’m a kind of a SQL guy, if you like. Anything directly related to SQL Server is what I really like working with, especially when it comes to tricky data-model questions and realization. I believe that well modeled and strictly implemented database integrity is a basis of any system of a perfect sound. There is so much to learn in the field; therefore I’ve started preparing myself for Microsoft exams.
I’m not a Web-guy for sure. I guess Web is the future, but at the moment I do not like working with web-apps.

You’re good at focusing on your projects. What’s your secret?
There is no secret – I like what I do and I do it with all responsibility. My projects are most often quite interesting and challenging. When your job is interesting and gives you satisfaction – that’s all you need to do it well. Even if I happen to work with some routine tasks, I always try making it interesting for myself.

You like taking pictures. Any favorite genre(s)?
Oh, yeah – I like the process of taking pictures. Especially when you are lucky enough to “catch” the right moment or emotion. I am always happy when I succeed in sharing the mood of the moment via a picture.
I was planning to organize something similar to a photo-club, which guys are having in Ølensvåg. Still have a desire for it, so – coming soon 🙂

Take a look at Aleksandr’s flickr album here.


Dynamic SQL

Brrrrrrhhhh, that sounds nasty. It is, but sometimes there are no other ways of solving a problem. Here’s a couple of tricks that might help you on your way, and some tricks on how to use static SQL where you might have thought you needed dynamic SQL.

SET NOCOUNT ON

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @TableName AS NVARCHAR(128)

SELECT name AS TableName, CAST(NULL AS INT) AS RowsCount
	INTO ##tables
	FROM sys.sysobjects
	WHERE xtype = 'U'

WHILE EXISTS (SELECT * FROM ##tables WHERE RowsCount IS NULL)
BEGIN
	SELECT TOP 1
	  @TableName = TableName
		FROM ##tables
		WHERE RowsCount IS NULL

	BEGIN TRY
		SET @SQL = 'UPDATE ##tables '
		SET @SQL = @SQL + '	SET RowsCount = (SELECT COUNT(*) FROM  [' + @TableName + '])'
		SET @SQL = @SQL + ' WHERE TableName = ''' + @TableName + ''''
		EXEC (@SQL)
	END TRY
	BEGIN CATCH
		UPDATE ##tables
			SET RowsCount = 0
			WHERE TableName = @TableName
	END CATCH
END

SELECT *
	FROM ##tables
	ORDER BY RowsCount DESC

DROP TABLE ##tables

This is one way of looping through tables, doing something with each. In this case doing a COUNT(*), but you can also use it to update statistics or similar.

There’s another way of doing it. NB! This is using sp_MSForEachTable which is an UNDOCUMENTED stored procedure, so use it on your own risk (it might get depricated with no notice!).

DECLARE @SQL AS NVARCHAR(MAX)

CREATE TABLE ##tables2 (
	TableName NVARCHAR(128),
	RowsCount INT
)

SET @SQL = 'INSERT INTO ##tables2 (TableName, RowsCount) '
SET @SQL = @SQL + 'SELECT ''?'', COUNT(*) FROM ?'
EXEC sp_MSForEachTable @SQL

SELECT *
	FROM ##tables2
	ORDER BY RowsCount DESC

DROP TABLE ##tables2

As you see, this is much less code. You can also do the same for databases with sp_MSForEachDb.

Another cool thing you can do with dynamic SQL is to get values back from it. In other words, we can rewrite our little script which counts records in each table to execute the COUNT in dynamic, but get the results from this and updating our table in static SQL. This way, we don’t have to use global (##tables) tables.

SET NOCOUNT ON

DECLARE @RowsCount AS INT
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @TableName AS NVARCHAR(128)

SELECT name AS TableName, CAST(NULL AS INT) AS RowsCount
	INTO #tables
	FROM sys.sysobjects
	WHERE xtype = 'U'

WHILE EXISTS (SELECT * FROM #tables WHERE RowsCount IS NULL)
BEGIN
	SELECT TOP 1
	  @TableName = TableName
		FROM #tables
		WHERE RowsCount IS NULL

	BEGIN TRY
		SET @SQL = 'SET @RowsCount = (SELECT COUNT(*) FROM [' + @TableName + '])'
		EXEC sp_executesql 
			@SQL, 
			N'@RowsCount INT OUTPUT', 
			@RowsCount OUTPUT

		UPDATE #tables
			SET RowsCount = @RowsCount
			WHERE TableName = @TableName
	END TRY
	BEGIN CATCH
		UPDATE #tables
			SET RowsCount = 0
			WHERE TableName = @TableName
	END CATCH
END

SELECT *
	FROM #tables
	ORDER BY RowsCount DESC

DROP TABLE #tables

5 minutes interview #26 – Matthew Velic

About a year ago I saw a video with a guy entering a “SQL Cruise contest” on twitter. At first I thought it was a joke, but it was well made and it made me smile. After that I kept seeing @mvelic and started reading his blog. He’s active in the SQL community (both on twitter and in real life), and has written several good blog-posts.

So, who are you Matt?
I’m a just a guy, originally from Cleveland, OH, and now living and working in Washington, DC. I moved here during college: I majored in Fine Art. I built sculptures and did installations with video and websites and even yarn. After graduation I made the conscious decision to not starve to death, and so I laid down my tools and went to work. I temped around DC, as is fairly average for graduates, but I was a step ahead of most because I had worked while in college. Most of my experience was in fund raising support and office administration.

How did you end up as a DBA?
Well, that’s where the story gets interesting. My final temp gig landed me at the Elizabeth Glaser Pediatric AIDS Foundation. They needed help through a database conversion and when I showed some aptitude for that project, they hired me full time to do data entry and fund raising support. A little over a year into the position, the official DBA was let go and his database administration tasks fell to me. At first I really didn’t know what I was doing, but with the community’s help, I was able to overcome many of my shortcomings in a small timeframe.

How do you find time to be active in the community?
I won’t lie, it’s tough sometimes. There are nights (and weekends) where I want to pick up the Xbox controller rather than work on blog posts or troll the internet for new SQL Server resources. But I enjoy doing it. I like to write, and I like to teach, and I like to share what I find. I hope I can continually get better at all these things.
And I won’t claim to be unselfish either: sharing can be a career growing activity. But as I came from an untechnical, temp-job background, I’m so glad to have a career!

If you should pick one thing, what’s the best thing about an active community?
There are so many great things about an active SQL Server community. Based on where I’m at in my life right now, the Best Thing is the feeling of support and camaraderie. I’m a one-man shop covering our fund raising systems. But I have no support. No one to ask advice, kick around ideas, or make plans. Especially through Twitter, I can get this support from the community and it’s filled that vacuum.

What do you do when you’re not in front of your computer(s)?
There’s non-computer time? Lately I’ve been reading fiction again: finished The Book of Lost Things by John Connolly, The Magicians by Lev Grossman and working on Snow Crash by Neal Stephenson. If you couldn’t tell, I’m a bit of a sci-fi and fantasy nut. I’ve also been known to partake in strategic board games from time to time, and I do enjoy good food (home cooked or going out). And when things get heavy, I break out the acoustic.

Blog: http://mattvelic.com/
Twitter: http://twitter.com/mvelic

The OVER clause

To use the ROW_NUMBER, RANK, DENSE_RANK or NTILE functions you need to use the OVER clause, but did you know you can use it on other functions as well? Like SUM, AVG, MAX etc. In the example under I want to list all timesheet records, but I want to know if the person has 7.5 hours each day, so I’ve made a “SumDailyManHours” column in the SELECT. This is easily done with SUM(ManHours) OVER (PARTITION BY PersonID, TimesheetDate). In the “old” days I’d do something like the second SELECT statement.

CREATE TABLE #Timesheet (
	PersonID NVARCHAR(8),
	ProjectID NVARCHAR(10),
	TimesheetDate DATE,
	ManHours DECIMAL(4, 2)
)

INSERT INTO #Timesheet
  (PersonID, ProjectID, TimesheetDate, ManHours)
VALUES
  ('Vidar', 'Customer1', '2010-12-01', 1),
  ('Vidar', 'Customer2', '2010-12-01', 5),
  ('Vidar', 'Customer1', '2010-12-02', 1),
  ('Vidar', 'Customer2', '2010-12-02', 6.5),
  ('Werner', 'Customer1', '2010-12-01', 7.5),
  ('Werner', 'Customer2', '2010-12-02', 7.5)

-- New way of doing it
SELECT
  *,
  SUM(ManHours) OVER (PARTITION BY PersonID, TimesheetDate) AS SumDailyManHours
	FROM #Timesheet

-- Old way of doing it
SELECT
  *,
  (SELECT SUM(ManHours)
		FROM #Timesheet
		WHERE PersonID = T.PersonID
		  AND TimesheetDate = T.TimesheetDate) AS SumDailyManHours
	FROM #Timesheet AS T

DROP TABLE #Timesheet

Here’s the result for both SELECT statements:

PersonID ProjectID TimesheetDate ManHours SumDailyManHours
Vidar Customer1 2010-12-01 1.00 6.00
Vidar Customer2 2010-12-01 5.00 6.00
Vidar Customer1 2010-12-02 1.00 7.50
Vidar Customer2 2010-12-02 1.00 7.50
Werner Customer1 2010-12-01 7.50 7.50
Werner Customer2 2010-12-02 7.50 7.50

SQL Server 2011 – Denali

Almost a month ago, Microsoft released the first CTP (Community Technology Preview) version of their upcoming SQL Server 2011, code-named Denali. I immediately downloaded and installed it on all my machines, and I’ve been using it ever since. It works perfectly with both 2005 and 2008, and of course with 2011.

Now, what’s new in this release? The biggest difference is that Management Studio is built into Visual Studio. Or, more correct, Management Studio has got a Visual Studio 2010-shining. I think this is a good call. For instance, I HATE the intellisense in 2008. It’s the first thing I remove when I’ve installed it, but in 2011, I haven’t turned it off yet, because it’s not in my way. It’s actually helping me write queries! The other big new UI related feature is snippets. Now you can, finally, have them inside Management Studio. I’m going to create a library of my scripts and make a snippet folder to share with all that’s interested. I’ll post it here on my blog when it’s ready.

When it comes to T-SQL enhancements, there’s actually a couple of new features that looks very interesting:

ORDER BY has some new features. With the following query, you get 10 rows. Not the first 10, but from row 10 (OFFSET 10) to 19 (FETCH NEXT 10). I think this will be easier to use for paging than using ROW_NUMBER() in 2008.

SELECT *
	FROM sys.sysobjects
	ORDER BY name
	OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Notice that in books online they specify that FETCH FIRST and FETCH NEXT are synonyms and that they’re there to provide ANSI compatibility.

CREATE SEQUENCE is a completely new T-SQL feature. The following query makes a sequence that you can poll.

CREATE SEQUENCE test
	AS INT
	START WITH 1
	INCREMENT BY 1

As you see, the script is not refering to any tables, which means you can use this sequence anywhere by using NEXT VALUE FOR MySequence. For instance:

INSERT INTO MyTable
  (Field1, Field2)
VALUES
  (NEXT VALUE FOR MySequence, 'Value2')

You can also use this feature as a default constraint. This could be helpful when identity-columns isn’t good enough.

SELECT TOP .. WITH TIES

Ever wanted to query a table and get all records that have one of the top 3 values? Let’s say you have a Sales-table where all your sales are stored. You want to find the top 3 sales, but you want to include “ties”. Let me show you with code.

DECLARE @Sales TABLE (
    Customer NVARCHAR(200),
    TotalSum FLOAT
)

INSERT INTO @Sales
  (Customer, TotalSum)
VALUES
  ('Statoil', 4000000),
  ('Hydro', 1000000),
  ('Kiwi', 100000),
  ('Mega', 100000),
  ('Ølen IL', 20)

SELECT TOP (3) WITH TIES
  *
    FROM @Sales
    ORDER BY TotalSum DESC

The results will be 4 rows:

Customer TotalSum
Statoil 4000000
Hydro 1000000
Kiwi 100000
Mega 100000

Pretty neat?

Unused Indexes

Two weeks ago, I held a session called “Hardcore SQL” session at AppEd where I (among other things) said that you should always start with removing unused indexes, before adding missing indexes. You can find my blog post about missing indexes here. So, how do you find unused indexes?

SELECT
  O.name AS ObjectName, I.name AS IndexName,
  user_seeks + user_scans + user_lookups AS Reads,
  user_updates AS Writes, P.rows AS RowsNum
    FROM sys.dm_db_index_usage_stats AS IUS
    INNER JOIN sys.indexes AS I ON I.index_id = IUS.index_id AND IUS.object_id = I.object_id
    INNER JOIN sys.partitions AS P ON P.index_id = IUS.index_id AND IUS.object_id = P.object_id
    INNER JOIN sys.objects AS O ON IUS.object_id = O.object_id
    WHERE O.type = 'U'
      AND IUS.database_id = DB_ID()
      AND I.type_desc = 'nonclustered'
      AND I.is_primary_key = 0
      AND I.is_unique_constraint = 0
      AND P.rows > 10000
    ORDER BY Reads, RowsNum DESC

Btw, I’ve got a Live Mesh folder with SQL scripts. If anyone wants access to it (and maybe wants to add scripts) you know how to contact me.

Auto Populate Value Lists

In AppFrame we’ve got something called value lists. These are meant to make filtering even easier. When creating your filter in for example a “Documents” CR3 form, and you choose the field “Responsible”, this of course have a foreign key to some kind of Persons-table. When choosing Responsible, why not produce a list of Persons so that you can click on the person instead of having to type in the PersonID or what ever is making it unique? Well, that’s the point of value lists. I just created a script to auto populate these, based on the foreign keys. This will only take the FKs that are bound on one column though! I’ll probably fix that tomorrow, but till then, here’s the script:

DECLARE
  @ForeignKeyBaseTable AS NVARCHAR(128),
  @PrimaryKeyTable AS NVARCHAR(128),
  @ForeignKeyColumn AS NVARCHAR(128),
  @PrimaryKeyColumn AS NVARCHAR(128),
  @ColumnName AS NVARCHAR(128),
  @ColumnLength AS SMALLINT,
  @IsNullable NVARCHAR(5),
  @ColumnType NVARCHAR(15),
  @SQL AS NVARCHAR(MAX)

DECLARE @SelectColumns AS TABLE (
	ColumnName NVARCHAR(128),
	ColumnLength INT,
	IsNullable NVARCHAR(5)
)

SET NOCOUNT ON

SELECT
  O.name AS ForeignKeyBaseTable,
  OBJECT_NAME(FK.referenced_object_id) AS PrimaryKeyTable,
  FKC.constraint_column_id AS ColumnNumber,
  FKColumn.name AS ForeignKeyColumn,
  PKColumn.name AS PrimaryKeyColumn,
  CASE WHEN T.name IN ('tinyint', 'smallint', 'int') THEN 'Int32'
		WHEN T.name IN ('smalldatetime', 'datetime', 'date', 'time') THEN 'DateTime'
		WHEN T.name IN ('float', 'decimal', 'numeric') THEN 'Long'
		WHEN T.name IN ('bigint') THEN 'Int64'
		ELSE NULL
  END AS ColumnType,
  FKColumn.max_length AS ColumnLength,
  CASE WHEN FKColumn.is_nullable = 0 THEN 'false' ELSE 'true' END AS IsNullable
	INTO #FKColumns
	FROM sys.foreign_keys AS FK
	INNER JOIN sys.objects AS O ON FK.parent_object_id = O.object_id
	INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id
	INNER JOIN sys.columns AS FKColumn
		ON FKColumn.object_id = FKC.parent_object_id
		  AND FKColumn.column_id = FKC.parent_column_id
	INNER JOIN sys.columns AS PKColumn
		ON PKColumn.object_id = FKC.referenced_object_id
		  AND PKColumn.column_id = FKC.referenced_column_id
	INNER JOIN sys.types AS T
		ON FKColumn.system_type_id = T.system_type_id
		  AND FKColumn.user_type_id = T.user_type_id
	WHERE T.name IN ('tinyint', 'smallint', 'int', 'smalldatetime',
					'datetime', 'date', 'time', 'float', 'decimal',
					'numeric', 'bigint', 'varchar', 'nvarchar')
	  AND O.type = 'U'
	  AND O.name LIKE 'atbl_%'
	  AND NOT EXISTS (SELECT *
						FROM sys.foreign_key_columns
						WHERE constraint_object_id = FK.object_id
						  AND constraint_column_id > 1)
	ORDER BY O.name, OBJECT_NAME(FK.referenced_object_id), FKC.constraint_column_id

WHILE EXISTS (SELECT * FROM #FKColumns)
BEGIN
	SELECT TOP 1
	  @ForeignKeyBaseTable = ForeignKeyBaseTable,
	  @PrimaryKeyTable = PrimaryKeyTable,
	  @ForeignKeyColumn = ForeignKeyColumn,
	  @PrimaryKeyColumn = PrimaryKeyColumn,
	  @ColumnType = ColumnType,
	  @ColumnLength = ColumnLength,
	  @IsNullable = IsNullable
		FROM #FKColumns

	SET @SQL = '<?xml version="1.0" encoding="utf-16"?>
<afRecordSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <IsFilterChanging>false</IsFilterChanging>
  <UniqueTable>' + @PrimaryKeyTable + '</UniqueTable>
  <ViewName>' + REPLACE(@PrimaryKeyTable, 'atbl_', 'atbv_') + '</ViewName>
  <SelectColumns>
	<afSelectColumn>
	  <Name>' + @PrimaryKeyColumn + '</Name>
	  <DisplayFormatType>0</DisplayFormatType>
	  <DisplayWidth>0</DisplayWidth>
	  <DisplayAlignment>0</DisplayAlignment>
	  <FieldAlias />'

	IF @ColumnType IS NOT NULL
	BEGIN
		SET @SQL = @SQL + '	  <DataType>' + @ColumnType + '</DataType>'
	END

	SET @SQL = @SQL + '
	  <Size>' + CAST(@ColumnLength AS NVARCHAR(128)) + '</Size>
	  <Nullable>' + @IsNullable + '</Nullable>
	</afSelectColumn>'


	INSERT INTO @SelectColumns
	  (ColumnName, ColumnLength, IsNullable)
	SELECT
	  name, max_length, CASE WHEN is_nullable = 1 THEN 'true' ELSE 'false' END
		FROM sys.columns
		WHERE object_id = OBJECT_ID(@PrimaryKeyTable)
		  AND (name LIKE '%Description%'
			OR REPLACE(name, 'ID', '') LIKE REPLACE(@PrimaryKeyColumn, 'ID', ''))
		  AND name <> @PrimaryKeyColumn

	WHILE EXISTS (SELECT * FROM @SelectColumns)
	BEGIN
		SELECT TOP 1
		  @ColumnName = ColumnName,
		  @ColumnLength = ColumnLength,
		  @IsNullable = IsNullable
			FROM @SelectColumns

		SET @SQL = @SQL + '
	<afSelectColumn>
	  <Name>' + @ColumnName + '</Name>
	  <DisplayFormatType>0</DisplayFormatType>
	  <DisplayWidth>0</DisplayWidth>
	  <DisplayAlignment>0</DisplayAlignment>
	  <FieldAlias />
	  <Size>' + CAST(@ColumnLength AS NVARCHAR(128)) + '</Size>
	  <Nullable>' + @IsNullable + '</Nullable>
	</afSelectColumn>'

		DELETE
			FROM @SelectColumns
			WHERE ColumnName = @ColumnName
	END


	SET @SQL = @SQL + '
  </SelectColumns>
  <OrderByColumns>
	<afOrderByColumn>
	  <Name>' + @PrimaryKeyColumn + '</Name>
	</afOrderByColumn>
  </OrderByColumns>
  <GroupByColumns />
  <WhereClause />
  <FilterString />
  <DistinctRows>false</DistinctRows>
  <MaxRecords>0</MaxRecords>
  <IgnoreDesignTimeMaxRecords>false</IgnoreDesignTimeMaxRecords>
  <MasterChildCriteria />
  <DisableAutoLoad>false</DisableAutoLoad>
  <InitMode>false</InitMode>
</afRecordSource>'

	UPDATE stbl_Database_Fields
		SET
		  ValueListRecordSource = @SQL,
		  ValueListValueMember = @PrimaryKeyColumn
		WHERE DBObjectID IN (@ForeignKeyBaseTable,
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'atbv_'),
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'atbx_'),
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'aviw_'))
		  AND FieldName = @ForeignKeyColumn

	DELETE
		FROM #FKColumns
		WHERE ForeignKeyBaseTable = @ForeignKeyBaseTable
		  AND PrimaryKeyTable = @PrimaryKeyTable
		  AND ForeignKeyColumn = @ForeignKeyColumn
		  AND PrimaryKeyColumn = @PrimaryKeyColumn
END

DROP TABLE #FKColumns