BEGIN TRAN

CREATE TABLE yourTable (yourTableID INT IDENTITY, column1 VARCHAR(500), column2 VARCHAR(500))

INSERT INTO yourTable (column1)

SELECT '2 * 2+1 * 7'

UNION ALL SELECT '1 * 1+2 * 4'

UNION ALL SELECT '1 *...

Theoretically, you could have 32,767 databases per instance.

Good luck with that. . . Personally, I find it difficult to keep track of more than 10.

I'm just reiterating the opinions already posted; answer other...

Untested, but something like this should work

BEGIN TRAN

DECLARE @sql AS VARCHAR(MAX)

SELECT @sql = COALESCE(@SQL,'') + 'SELECT ' + c.name + ', '''+c.name+''' AS columnName FROM ' + o.name + '...

mick burden (1/9/2012)

---

Many thanks, I've tried your solutions and the first dynamic version from Cadavre works despite the fact syscolumns is in the query, when I substitute this with sys.columns...

mick burden (1/9/2012)

---

There is one more question regarding this, is there a way this can be achieved without specifying the column names (other than the date field) as I may...

fahey.jonathan (1/9/2012)

---

Another possible way to accomplish what you need is to use subqueries to get the parts and left join them to the main table:

SELECT e.emp_id, e.emp_name, e.join_date,

...

BEGIN TRAN

--Sample data

CREATE TABLE yourTable ([date] DATE, Jim...

BEGIN TRAN

--1,000,000 Random(-ish) rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,

CONVERT(DATE,RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME)) AS randomDate

INTO #testEnvironment

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3

--Query the table

SELECT TOP...

The tables as you've laid them out are badly designed and incorrect.

I've corrected the issue, but not the bad design : -

CREATE TABLE role(

[roleID] INT IDENTITY,

[applicationname] [nvarchar](256) ,

[rolename] [nvarchar](256))

INSERT INTO...

This should get you on the right sort of track. Bear in mind that I've knocked it together very quickly and am fairly certain that there's a bug in...

Because your EXEC only exists in relation to itself, it doesn't affect anything outside of it.

Try this, hopefully it'll...

I prefer to avoid CURSORs and loops when they aren't necessary, including the hidden one in sp_MsForeachdb.

So how about this?

DECLARE @TABLE VARCHAR(200), @sql VARCHAR(MAX)

--Add your table name here

SET @TABLE =...

Sorry, should have said that it doesn't work with IE (well, can't get it to work with 8 or 9)....

Brandie Tarvin (1/5/2012)

---

Does anyone else have issues with the Edit Signature page on the control panel? It only ever shows 1 line of the .sig in the edit window and...