August 8, 2013 at 4:51 pm
Comments posted to this topic are about the item Display the SELECT statement for any table
Mehernosh.
August 12, 2013 at 8:13 am
You should add the schema of the table. That would be better.
August 13, 2013 at 9:38 am
Fair point HextallFanForLife.
You can try the following if you want the Schema name to be included too.
DECLARE @mStrColumns VARCHAR(8000) , @mStrTable VARCHAR(100)
SET @mStrTable ='TABLE_NAME'
--
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.'TABLE_NAME'
FROM [SYSCOLUMNS] SC
JOIN SYS.OBJECTS SO ON SC.ID = SO.object_ID
WHERE SC.STATUS <> COALESCE (NULL ,SC.STATUS+1) AND SO.NAME = @mStrTable
--
SELECT 'SELECT ' + @mStrColumns + ' FROM ' + @mStrTable RESULTS
Please replace the "'TABLE_NAME" in the script with your Table Name.
Hope this helps 🙂
Mehernosh.
August 22, 2013 at 4:37 am
Mehernosh (8/13/2013)
Fair point HextallFanForLife.You can try the following if you want the Schema name to be included too.
DECLARE @mStrColumns VARCHAR(8000) , @mStrTable VARCHAR(100)SET @mStrTable ='TABLE_NAME'
--
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.'TABLE_NAME'
FROM [SYSCOLUMNS] SC
JOIN SYS.OBJECTS SO ON SC.ID = SO.object_ID
WHERE SC.STATUS <> COALESCE (NULL ,SC.STATUS+1) AND SO.NAME = @mStrTable
--
SELECT 'SELECT ' + @mStrColumns + ' FROM ' + @mStrTable RESULTS
Please replace the "'TABLE_NAME" in the script with your Table Name.
Hope this helps 🙂
I believe that you want the SELECT statement in the above script to read as follows:
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.' + 'TABLE_NAME'
Otherwise, there is an error and the command does not execute.
Of course, as the author explains, you should replace TABLE_NAME with the actual name of the table you're after.
August 22, 2013 at 6:34 am
I got a syntax error from the text on the second example.
Change the line
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.'TABLE_NAME'
To:
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.' + @mStrTable
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 22, 2013 at 6:59 am
This is just what I need to keep me from using select * when doing a quick extract that sometimes ends up being used repeatedly.
August 22, 2013 at 7:44 am
Why don't you just right click on the table name in SSMS in the object explorer and do 'Script table as' then select 'SELECT to'. This will do the same thing plus format the results.
Am I missing something?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 22, 2013 at 9:42 am
Thanks Ken Hedges
---------------------------------
---Complete working example---
---Apologies for any mix ups.
---------------------------------
--Create a test table & insert test record.
Create Table TestSelect (tstField1 Varchar(10), tstField2 Varchar(10), tstField3 Varchar(10))
Insert into TestSelect Select 'A', 'A1', 'A100'
--Cheking the Table
SELECT * FROM TESTSELECT
---------------------------------
----Main query----
---------------------------------
DECLARE @mStrColumns VARCHAR(8000) , @mStrTable VARCHAR(100)
SET @mStrTable ='testSelect'
--
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.' + SO.NAME
FROM [SYSCOLUMNS] SC
JOIN SYS.OBJECTS SO ON SC.ID = SO.object_ID
WHERE SC.STATUS <> COALESCE (NULL ,SC.STATUS+1) AND SO.NAME = @mStrTable
--
SELECT 'SELECT ' + @mStrColumns + ' FROM ' + @mStrTable RESULTS
---The results as expected.
--SELECT [tstField1], [tstField2], [tstField3] FROM dbo.TestSelect
--Remember to drop the table afterwards.
Drop table testSelect
Hope this helps.
Mehernosh.
August 22, 2013 at 9:45 am
Tom_Sacramento
---------------------------------
---Complete working example---
---Apologies for any mix ups.
---------------------------------
--Create a test table & insert test record.
Create Table TestSelect (tstField1 Varchar(10), tstField2 Varchar(10), tstField3 Varchar(10))
Insert into TestSelect Select 'A', 'A1', 'A100'
--Cheking the Table
SELECT * FROM TESTSELECT
---------------------------------
----Main query----
---------------------------------
DECLARE @mStrColumns VARCHAR(8000) , @mStrTable VARCHAR(100)
SET @mStrTable ='testSelect'
--
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']' ,@mStrTable = schema_Name(schema_id) + '.' + SO.NAME
FROM [SYSCOLUMNS] SC
JOIN SYS.OBJECTS SO ON SC.ID = SO.object_ID
WHERE SC.STATUS <> COALESCE (NULL ,SC.STATUS+1) AND SO.NAME = @mStrTable
--
SELECT 'SELECT ' + @mStrColumns + ' FROM ' + @mStrTable RESULTS
---The results as expected.
--SELECT [tstField1], [tstField2], [tstField3] FROM dbo.TestSelect
--Remember to drop the table afterwards.
Drop table testSelect
Hope this helps.
Mehernosh.
August 22, 2013 at 9:49 am
below86
below86
Why don't you just right click on the table name in SSMS in the object explorer and do 'Script table as' then select 'SELECT to'. This will do the same thing plus format the results.
Am I missing something?
Perfectly fine and valid. You are not missing anything out.
Just wanted a SQL script to do the needful - hence the post.
Mehernosh.
August 22, 2013 at 10:23 am
Thank you for the script. I have updated this to a stored procedure that I can pass in the table name:
CREATE PROCEDURE dbo.UTIL_DynamicTable_SELECT
@mStrTable varchar(255)
AS
-----------------------------------------------
--Display the SELECT Statement for any Table--
-----------------------------------------------
DECLARE @mStrColumns VARCHAR(8000)
, @vSQLString nvarchar(max)
, @ParmDefinition nvarchar(500)
, @SQLResults AS NVARCHAR(250)
BEGIN
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']'
FROM [SYSCOLUMNS] SC INNER JOIN [SYSOBJECTS] SO ON SC.ID = SO.ID
WHERE SC.STATUS <> COALESCE (NULL ,SC.STATUS+1) AND SO.NAME = @mStrTable
SELECT @vSQLString = 'SELECT ' + @mStrColumns + ' FROM ' + @mStrTable
SET @ParmDefinition = N'@SQLResultsOut NVARCHAR(max) OUTPUT';
exec sp_executesql @vSQLString, @ParmDefinition,@SQLResultsOut = @SQLResults OUTPUT
END
Owen White
August 22, 2013 at 12:47 pm
I guess the real draw would be if you wanted to automate a series of events. I'm probably not coming up with the best example but maybe using this to generate dynamic SQL and bolting on a few extra statements to SELECT INTO.
SET NOCOUNT ON
DECLARE @schemaname VARCHAR(128),
@objname VARCHAR(128)
SELECT @schemaname = 'dbo',
@objname = 'ServerList'
SELECT
(SELECT 'SELECT '
+ STUFF((
SELECT ',' + QUOTENAME(sc.[name])
FROM [sys].[syscolumns] sc
INNER JOIN sys.objects so
ON sc.[id] = so.[object_id]
WHERE so.[object_id] = OBJECT_ID(@objname)
AND so.[schema_id] = SCHEMA_ID(@schemaname)
ORDER BY sc.[colid]
FOR XML PATH('')), 1, 1, '')
)
+ (SELECT ' INTO '
+ QUOTENAME(@schemaname)
+ '.'
+ QUOTENAME(@objname
+ '_'
+ CONVERT(CHAR(8), GETDATE(), 112))
+ ' FROM '
+ QUOTENAME(@schemaname)
+ '.'
+ QUOTENAME(@objname)
+ ';'
)
SET NOCOUNT OFF
GO
August 22, 2013 at 1:10 pm
Thanks 'You name Here', its been a good day for gaining new knowledge. Not real sure where or when I would need to do this but I will try and remember this.
Thanks,
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 23, 2013 at 2:24 am
That's great Owen White .
Thank you for the script. I have updated this to a stored procedure that I can pass in the table name:
CREATE PROCEDURE dbo.UTIL_DynamicTable_SELECT
@mStrTable varchar(255)
AS
-----------------------------------------------
--Display the SELECT Statement for any Table--
-----------------------------------------------
DECLARE @mStrColumns VARCHAR(8000)
, @vSQLString nvarchar(max)
, @ParmDefinition nvarchar(500)
, @SQLResults AS NVARCHAR(250)
BEGIN
SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + '[' + CAST(SC.[NAME] AS VARCHAR(100) ) +']'
FROM [SYSCOLUMNS] SC INNER JOIN [SYSOBJECTS] SO ON SC.ID = SO.ID
WHERE SC.STATUS <> COALESCE (NULL ,SC.STATUS+1) AND SO.NAME = @mStrTable
SELECT @vSQLString = 'SELECT ' + @mStrColumns + ' FROM ' + @mStrTable
SET @ParmDefinition = N'@SQLResultsOut NVARCHAR(max) OUTPUT';
exec sp_executesql @vSQLString, @ParmDefinition,@SQLResultsOut = @SQLResults OUTPUT
END
Owen White
Nicely done.
Mehernosh.
August 23, 2013 at 2:27 am
Hey "Your Name Here" thanks for the example.
I guess the real draw would be if you wanted to automate a series of events. I'm probably not coming up with the best example but maybe using this to generate dynamic SQL and bolting on a few extra statements to SELECT INTO.
We can use a bit of Dynamic SQL or create it as a SP or UDF or .... the possibilities are there for us to explore.
🙂
Great stuff.
Mehernosh.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply