Tag: SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things I needed to do recently was move some objects from one schema to another. I wrote about moving an object between schemas recently, but another part of that process was finding  the objects to move.

This is a quick post on how to find the objects in a schema. To start, here are a number of objects in a test database.

A schema has a name, which is the way that we would search for related objects. That means I want a parameter for my query, so I’ll start with a variable to store the name. For me, I’ll use a well named variable like this:

DECLARE @schema VARCHAR(100) = 'SallyDev';

Now I have a schema name, where do I find schema data? There is a DMV called sys.schemas, which contains a bit of meta data. If I query that, I see this:

I can see my SallyDev schema, so I know I’ll query this DMV.

The other information I need is the object data, which is in sys.objects. I query that for the various data I want, but I want to limit data by the schema. In sys.objects, there is a schema_id, which is the data I’ll join with from sys.schemas.

When I do that, I build a query like this:

DECLARE @schema VARCHAR(100) = 'SallyDev';

SELECT 
        o.type_desc,
        s.name AS 'Schema Name',
        o.name AS 'Object Name',
        o.object_id

FROM sys.objects o
     INNER JOIN sys.schemas s ON s.schema_id = o.schema_id

WHERE s.name = @schema;

I can execute that and I’ll see the objects I need.

SQLNewBlogger

This was a post related to the one on moving objects and I wrote this write after that one. It was only about 5 minutes longer to put this together, and it gives me a script I can easily search for on my blog if I need to do this task.

Once again, a quick and easy way to show some skills, practice explaining something, and get some knowledge stored for my own reference.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I haven’t had the need to move an object from one schema to another in years. Really since SQL Server 2000. I wrote about deleting a user that owns a schema recently, but that’s often a first step. The next thing I might need to do is actually move objects from that schema to a new one.

I actually ran across this command when I was looking how to move the schema to a new user. There’s actually a parameter for ALTER SCHEMA that will move objects. This is the TRANSFER argument and it works like this.

I need a new schema for the object. In this case, I’ve got a table called SallyDev.Class. I want to move this to a new schema, and I’ll choose dbo for this example. I often have had developers build in their own schema and then I’ll transfer to the dbo schema, which is almost like a merge of code from one branch (SallyDev) to another (dbo).

The format of the command is: ALTER SCHEMA <newschema> TRANSFER <object>

The new schema name is just the name, with brackets if needed. Hint, if you need brackets, rename your schema, please.

The object is the qualified name of the object, with the old schema. In this case, the command I’ll use is:

ALTER SCHEMA dbo TRANSFER SallyDev.Class

Here’s my before look:

When I run the code, it works:

Now my object is moved. Success!

SQLNewBlogger

This is a quick view of a specific skill that can be handy. I won’t use this often, but if my team worked in this flow, or we had an issue, this not only shows how to resolve a single item move, but also helps me remember the command. I hadn’t seen this before, so a quick 10 minute blog is useful.

This also gives me ideas for other blogs, like how to automate this for a number of objects.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

There have been lots of posts on the topic of generating random values, and some great articles. One of my favorites is Jeff Moden’s Generating Test Data: Part 1 – Generating Random Integers and Floats. Part 2 deals with dates, and that’s actually what I needed, but really I needed part 1.

In my situation, I was helping a customer generate some random data. They had filled a table, Customers, with some data.

The goal was to populate a child table with some data. The child table had a date column that was supposed to be between the Entered and Exit dates in the Customer table.

My update would have a join, obviously, and I can reference the enter and exit date, but how to get a date between them? My first thought was that I wanted a DATEADD() function. Something like this:

UPDATE ce

SET ce.EventTimeStamp = DATEADD( MINUTE, SomeRandomValue, c.CustomerExitedDateTime)), c.CustomerEnteredDateTime)

FROM   dbo.Customer AS c

INNER JOIN dbo.CustomerEvent AS ce 

ON ce.CustomerID = c.CustomerID

The trick is what random value to use? If you look through Jeff’s article, you will see that the trick is to use a tally table and the NEWID() function. However, this doesn’t work:

 UPDATE ce

SET ce.EventTimeStamp = DATEADD( MINUTE, NEWID(), c.CustomerEnteredDateTime)

FROM   dbo.Customer AS c

INNER JOIN dbo.CustomerEvent AS ce 

ON ce.CustomerID = c.CustomerID

;

What I need to do is convert the GUID to a number. In this case, I added CHECKSUM around it, again, as in Jeff’s article. Then use ABS() to enclose this to get all positive numbers.

 UPDATE ce

SET ce.EventTimeStamp = DATEADD( MINUTE, ABS(CHECKSUM((NEWID())))), c.CustomerEnteredDateTime)

FROM   dbo.Customer AS c

INNER JOIN dbo.CustomerEvent AS ce 

ON ce.CustomerID = c.CustomerID

;

This gives me values, but they aren’t constrained. What I need to do is limit the upper random value so that the end time doesn’t exceed the Customer.CustomerExitDateTime for that row.

To do this, I can constraint a large set of numbers to some value with the modulo function. This will limit what values can appear. The basic script is this:

UPDATE ce

SET ce.EventTimeStamp = DATEADD( MINUTE, ABS(CHECKSUM((NEWID())))) % 10, c.CustomerEnteredDateTime)

FROM   dbo.Customer AS c

INNER JOIN dbo.CustomerEvent AS ce 

ON ce.CustomerID = c.CustomerID

;

This would give me values between 1 and 0 minutes after the start time, but this doesn’t mean these values won’t be after the exit time. This is also an unrealistic window if most of the time the enter and exit times vary by hours.

What I did instead was to use the difference between the enter and exit times, with DATEDIFF() as my modulo function. That gives me:

WITH myTally (n)

AS

-- SQL Prompt formatting off

(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)

)

UPDATE ce

SET ce.EventTimeStamp = DATEADD( MINUTE, ABS(CHECKSUM((NEWID()))) % (DATEDIFF(MINUTE, c.CustomerEnteredDateTime, c.CustomerExitedDateTime)), c.CustomerEnteredDateTime)

FROM   dbo.Customer AS c

INNER JOIN dbo.CustomerEvent AS ce 

ON ce.CustomerID = c.CustomerID

;

I run this, and I get the table updated with a random set of values.

SQLNewBlogger

This was a problem in my daily work. It was a customer, but it could easily be an internal query problem. I spent about 10 minutes grabbing screen shots and taking apart the query I’d built.

You can do this, too. Show us your mind working with the solutions you write in your own blog.