Storing the result of a stored procedure to a table

• Santhoshkumar KB

  SSCertifiable

  Points: 5066

  More actions

  February 12, 2009 at 2:39 am

  #128725

  Hi,

  I need to store the result of below statement to a table

  exec sp_tables

  how can I do this in SQL Server 2005?

  (Instead this can be achieved by querying

  INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS VIEW

  Actually I need

  exec sp_tables_ex 'Access_linked'

  and

  exec sp_columns_ex 'Access_linked'

  so that I can get the list of tables, columns and column data types to a table

  Thanks

  Thanks,
  Santhosh
  

  ---

  Human Knowledge Belongs To The World !!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 12, 2009 at 3:12 am

  #942668

  This should put you on the right track...

  IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL

  DROP TABLE #Worksheets

  CREATE TABLE #Worksheets (TABLE_CAT varchar(30), TABLE_SCHEM VARCHAR(30), TABLE_NAME VARCHAR(50), TABLE_TYPE VARCHAR(20), REMARKS VARCHAR(30))

  INSERT INTO #Worksheets

  EXEC sp_tables_ex @ServerName

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Santhoshkumar KB

  SSCertifiable

  Points: 5066

  More actions

  February 12, 2009 at 3:36 am

  #942680

  Chris Morris (2/12/2009)

  ---

  This should put you on the right track...

  IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL

  DROP TABLE #Worksheets

  CREATE TABLE #Worksheets (TABLE_CAT varchar(30), TABLE_SCHEM VARCHAR(30), TABLE_NAME VARCHAR(50), TABLE_TYPE VARCHAR(20), REMARKS VARCHAR(30))

  INSERT INTO #Worksheets

  EXEC sp_tables_ex @ServerName

  Yes I got it...

  For these kind of things we initially need to know how many columns the result set will be having right?

  Thanks

  Thanks,
  Santhosh
  

  ---

  Human Knowledge Belongs To The World !!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 12, 2009 at 3:39 am

  #942681

  Santhosh (2/12/2009)

  ---

  Yes I got it...

  For these kind of things we initially need to know how many columns the result set will be having right?

  Thanks

  Yes, and you can easily do that by examining the sproc.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Santhoshkumar KB

  SSCertifiable

  Points: 5066

  More actions

  February 12, 2009 at 3:44 am

  #942686

  Hi

  In Linked Server(Access database)

  Can we know which columns are having ntext data type?

  (when i went through the sproc sp_columns_ex i came to know that

  MEMO data type of Access will be internally converted to ntext instead of nvarchar(max) which we are not able to compare with other table)

  Thanks

  Thanks,
  Santhosh
  

  ---

  Human Knowledge Belongs To The World !!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 12, 2009 at 5:59 am

  #942752

  Santhosh (2/12/2009)

  ---

  Hi

  In Linked Server(Access database)

  Can we know which columns are having ntext data type?

  (when i went through the sproc sp_columns_ex i came to know that

  MEMO data type of Access will be internally converted to ntext instead of nvarchar(max) which we are not able to compare with other table)

  Thanks

  You've answered your own question here Santosh. In any case, you'd import into a staging table, right? Then you would move the data into your destination table with columns and datatypes of your choosing...

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden