December 27, 2014 at 10:04 am
Hi Team,
I need to retrieve columns names(instead of select * from) as single row from table in dynamic way.
i m using following query.
its working fine while using from selected database. but its not working when i m running from different database.
DECLARE @columnnames varchar(max)
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames
I need to pass database name dynamically like using by variable.
Is this possible to use variable after from clause. or any other methods? kindly suggest on it.
eg:
DECLARE @columnnames varchar(max)
DECLARE @variable='db_month11_2014'
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from @VARIABLE.INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames
thanks in advance.
Kannan.
December 27, 2014 at 5:37 pm
The following code will do it. It uses one form of dynamic SQL for most of it and another form for passing the impassible database name for the FROM clause.
Now... two way street here, please. Why do you need to do this? What is the end goal? I ask because I'm simply curious and I also want to tell you that this is normally a really bad thing to do from a GUI. It's like a poor-man's ORM for C.R.U.D. (Create, Retrieve, Update, and Delete) code.
You are aware that you can easily get all column names of a table all at once just by dragging the "Columns" folder for the table from the Object Explorer into the code window, yes?
--===== Variable declarations
DECLARE @pDBName SYSNAME --Could be a parameter for a stored proc
,@pTableName SYSNAME --Could be a parameter for a stored proc
,@ColumnNames VARCHAR(MAX) --Could be an output parameter for a stored proc
,@SQL NVARCHAR(MAX)
;
--===== Make sure the @pDBName (the only variable with concatenation properties in the dynamic SQL)
-- is actually a database name rather than SQL injection. The other two variables are fully
-- parameterized and of the correct length to prevent injection by truncation. Note that if
-- the database name does not exist, we do nothing but return so as to give no hint to a
-- a possible attacker. This makes the QuOTENAME thing I did further down total overkill
-- but I left that there anyway.
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = @pDBName)
RETURN
;
--===== Setup the variable contents including the "double-dynamic" SQL.
SELECT @pDBName = 'put_database_name_here'
,@pTableName = 'put_table_name_here'
,@SQL = REPLACE(REPLACE('
SELECT @ColumnNames = COALESCE(@ColumnNames+",","") + COLUMN_NAME
FROM <<@pDBName>>.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @pTableName
OPTION (MAXDOP 1);'
,'"' ,'''')
,'<<@pDBName>>' ,QUOTENAME(@pDBName)) --QUOTENAME() to help prevent SQL-INJECTION
;
--===== Get the column names from the desired database and table.
EXECUTE sp_executesql @SQL
, N'@pTableName SYSNAME, @ColumnNames VARCHAR(MAX) OUT' --Parameter Definitions
, @pTableName = @pTableName, @ColumnNames = @ColumnNames OUT --Value Assignment
;
--===== Here are the desired results
PRINT @ColumnNames;
--===== Here's the SQL that was executed
PRINT @SQL;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2014 at 10:25 am
Hi Jeff,
yes. we can easily get columns from GUI.
Thanks for your query. i changed the database name and table name but it returns nothing.
My intention is to do spill over(respective date data's need to be moved in respective date tables)
eg:
source database never change(its having day wise tables for current month)
table_20141201 to table_20141231
i have to move day-1 source table to day-1 destination table.
condition is source table having many date records. but destination records should having respective date records in respective date tables only.
source table count based on some date column:
source:
db name: staging_cdr
table: table_20141227 its having
date count
20141225 2000
20141226 1200
20141130 100
while its moving to destination 20141225th date records should be moved to hist_month11_2014.dbo.table_20141227 table like that for other date too.
every month some new columns might be added. so i need column list of which table need to insert.
below is my query:
declare @sql varchar(max)
declare @c_date varchar(max)--change
declare @columnslist nvarchar(max)
declare @month_db varchar(max)
declare @sday_table varchar(max)
declare @sday_month varchar(max)
declare @sday varchar(max)
declare @mindate int
declare @maxdate int
set @sday_table='table_'+CONVERT(varchar(8),getdate()-1,112)
set @sday_month='db_MONTH'+right((replace(CONVERT(varchar(20),getdate()-1,105),'-','_')),7)
set @sday=@sday_month+'.dbo.'+@sday_table
--step:select backdated dates in day-1 table
IF OBJECT_ID('etl_load.dbo.TEMP') IS NOT NULL
drop TABLE etl_load.dbo.TEMP
set @sql='
select distinct left(C_date,8)as c_date,identity(int,1,1)as IDX
into etl_load.dbo.TEMP from '+@sday+'(nolock)
order by left(C_date,8)'
exec (@sql)
--insert into table based on call_date
select @mindate=min(idx), @maxdate=max(idx) from etl_load.dbo.TEMP (nolock)
while @mindate<@maxdate
begin
select @c_date=left(call_date,8) from etl_load.dbo.TEMP(nolock) where idx=@mindate
set @month_db='hist_MONTH'+substring(@c_date,5,2)+'_'+left(@c_date,4)
---------------------------------------
--needed part
--select column list from table
select @columnslist= COALESCE(@columnslist,'') +column_name+','
FROM INFORMATION_SCHEMA.COLUMNS ---- need to pass @month_db before from clause
WHERE TABLE_NAME = 'cdr_'+@c_date+'' and table_catalog=@month_db
-------------------------------------------------------------
begin
set @sql='insert into '+@month_db+'.dbo.table_'+@c_date+'
select '+@columnslist+' from '+@sday+'(nolock)
where left(c_date,8)='''+@c_date+''''
print(@sql)
end
set @mindate=@mindate+1
end
Regards,
Kannan
December 28, 2014 at 12:13 pm
Quick suggestion, use the sp_describe_first_result_set (or the equal tvfn) to bring back the details of a result set, see the demo code below, should be enough to get you passed this hurdle. Quick question, why the NOLOCK hint, any specific reason?
😎
USE tempdb;
GO
/* Database name variable*/
DECLARE @DATABASE_NAME NVARCHAR(128) = N'msdb';
/* The query with the database name placeholder "{{@DATABASE_NAME}}"
replaced by the value of the @DATABASE_NAME variable
*/
DECLARE @SQL_STR NVARCHAR(MAX) = REPLACE(N'SELECT * FROM [{{@DATABASE_NAME}}].[dbo].[sysalerts]',N'{{@DATABASE_NAME}}',@DATABASE_NAME);
/* Table variable to catch the output of the sp_describe_first_result_set procedure
*/
DECLARE @RES TABLE
(
is_hidden BIT NULL
,column_ordinal INT NULL
,name SYSNAME NULL
,is_nullable BIT NULL
,system_type_id INT NULL
,system_type_name SYSNAME NULL
,max_length INT NULL
,precision INT NULL
,scale INT NULL
,collation_name SYSNAME NULL
,user_type_id INT NULL
,user_type_database SYSNAME NULL
,user_type_schema SYSNAME NULL
,user_type_name SYSNAME NULL
,assembly_qualified_type_nameSYSNAME NULL
,xml_collection_id INT NULL
,xml_collection_database SYSNAME NULL
,xml_collection_schema SYSNAME NULL
,xml_collection_name SYSNAME NULL
,is_xml_document BIT NULL
,is_case_sensitive BIT NULL
,is_fixed_length_clr_type BIT NULL
,source_server SYSNAME NULL
,source_database SYSNAME NULL
,source_schema SYSNAME NULL
,source_table SYSNAME NULL
,source_column SYSNAME NULL
,is_identity_column BIT NULL
,is_part_of_unique_key BIT NULL
,is_updateable BIT NULL
,is_computed_column BIT NULL
,is_sparse_column_set BIT NULL
,ordinal_in_order_by_list BIT NULL
,order_by_is_descending BIT NULL
,order_by_list_length BIT NULL
,tds_type_id INT NULL
,tds_length INT NULL
,tds_collation_id INT NULL
,tds_collation_sort_id INT NULL
)
INSERT INTO @RES
EXEC sp_describe_first_result_set @SQL_STR;
/* Full description of the result set */
SELECT * FROM @RES;
December 28, 2014 at 1:20 pm
Kannan Vignesh (12/28/2014)
Hi Jeff,Thanks for your query. i changed the database name and table name but it returns nothing.
That would be because I'm an idiot. :blush: I added the check for the database name without testing it and, of course, I put the check in before the variables were populated.
This will do it. And, yeah, I tested it this time.
--===== Variable declarations
DECLARE @pDBName SYSNAME --Could be a parameter for a stored proc
,@pTableName SYSNAME --Could be a parameter for a stored proc
,@ColumnNames VARCHAR(MAX) --Could be an output parameter for a stored proc
,@SQL NVARCHAR(MAX)
;
--===== Preset the variables for the database and table name
SELECT @pDBName = 'put_database_name_here'
,@pTableName = 'put_table_name_here'
;
--===== Make sure the @pDBName (the only variable with concatenation properties in the dynamic SQL)
-- is actually a database name rather than SQL injection. The other two variables are fully
-- parameterized and of the correct length to prevent injection by truncation. Note that if
-- the database name does not exist, we do nothing but return so as to give no hint to a
-- a possible attacker. This makes the QuOTENAME thing I did further down total overkill
-- but I left that there anyway.
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = @pDBName)
RETURN
;
--===== Setup the variable contents including the "double-dynamic" SQL.
SELECT @SQL = REPLACE(REPLACE('
SELECT @ColumnNames = COALESCE(@ColumnNames+",","") + COLUMN_NAME
FROM <<@pDBName>>.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @pTableName
OPTION (MAXDOP 1);'
,'"' ,'''')
,'<<@pDBName>>' ,QUOTENAME(@pDBName)) --QUOTENAME() to help prevent SQL-INJECTION
;
--===== Get the column names from the desired database and table.
EXECUTE sp_executesql @SQL
, N'@pTableName SYSNAME, @ColumnNames VARCHAR(MAX) OUT' --Parameter Definitions
, @pTableName = @pTableName, @ColumnNames = @ColumnNames OUT --Value Assignment
;
--===== Here are the desired results
PRINT @ColumnNames;
--===== Here's the SQL that was executed
PRINT @SQL;
Note that @pTableName cannot take a two part naming convention such as 'dbo.sometablename'. If that becomes a problem, we'll have to add a wee bit more code.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2014 at 1:30 pm
Okay, I have read the OPs posts and I am slightly confused at what he is attempting to accomplish. I get the idea that the OP wants the column names for a specified table (passed to the procedure), but what is the end game?
December 28, 2014 at 2:15 pm
Lynn Pettis (12/28/2014)
Okay, I have read the OPs posts and I am slightly confused at what he is attempting to accomplish. I get the idea that the OP wants the column names for a specified table (passed to the procedure), but what is the end game?
In a nutshell, the end game appears to be to archive data and to have the system automatically make corrections for newly added columns not only for the archive tables, but for the current month and daily tables, as well.
At this point, I would ask, how often does such a thing actually happen and is it really worth the effort to automate?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2014 at 2:39 pm
Jeff Moden (12/28/2014)
Lynn Pettis (12/28/2014)
Okay, I have read the OPs posts and I am slightly confused at what he is attempting to accomplish. I get the idea that the OP wants the column names for a specified table (passed to the procedure), but what is the end game?In a nutshell, the end game appears to be to archive data and to have the system automatically make corrections for newly added columns not only for the archive tables, but for the current month and daily tables, as well.
At this point, I would ask, how often does such a thing actually happen and is it really worth the effort to automate?
I have a stored procedure that loops through all the report tables that should move from one classified network over a guard system to another classified network (the database from the lower side is manually moved to the higher network for verification purposes on a daily basis until we can figure out the issues keeping all data from moving the proscribed method), moving all the data that did not move through the automated process. Since the tables have identical schema it isn't too difficult, but I need to generate the column list dynamically since I have to drop on column from the list since the data type is timestamp.
December 28, 2014 at 5:16 pm
yes. its working. Thank u so much..!
December 28, 2014 at 5:19 pm
Nolock doesn't allows to table get lock.
December 28, 2014 at 5:25 pm
Kannan Vignesh (12/28/2014)
Nolock doesn't allows to table get lock.
It was a rhetorical question to mostly suggest that it shouldn't be used in most cases. WITH(NOLOCK) is actually the proper form because the form without the WITH has been deprecated. It can also cause some major issues. For example, it is possible to return two rows for the same thing... one prior to an update and the dirty read after the update. It's not as uncommon as people would think and many folks have written demonstrable code on the subject. Since it's not likely that a SELECT will be interfered with on the underlying tables that the INFORMATION_SCHEMA views rely on, you might just want to leave it off to prevent the eventual duplication of rows from occurring.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2014 at 11:45 pm
Kannan Vignesh (12/28/2014)
Nolock doesn't allows to table get lock.
Are you experiencing locking or any other problems and that is why you are using NOLOCK? Or are you using it just in case there might be a problem?
😎
You might want to look at this excellent article by Aron Bertrand, Bad habits : Putting NOLOCK everywhere[/url]
December 30, 2014 at 3:17 pm
Nice story, Joe but what on Earth does that have to do with the problem of stepping though databases or tables?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2014 at 3:56 pm
CELKO (12/30/2014)
Besides begin better than anything you can write, the vendor will maintain it and the vendor will be legally responsible if anything goes wrong.
Bull! I'm telling you, don't try to play "it's the vendors fault I don't have my data" card in court. You'll just be laughed at and pitied.
You should test your archiving system continually whether it's your own or a vendor-provided one. Never let the vendor determine what dbs get tested and the timing of testing (except in specific cases for performance). If they need to do that, there's something not right with the product!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 5, 2015 at 11:21 pm
thanks for your clarification. i will correct it.
Ok jeff. with out nolock, if many peoples accessing same table means it cause any performance issue..?
thanks
Kannan.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply