Link to home
Start Free TrialLog in
Avatar of Tyler
TylerFlag for United States of America

asked on

Clone table from one server.database to another server.database

clonetable.sql
I am wanting to copy a table, its structure, indexes primary keys constraints etc.  how can this be done via t-script or Stored procedure.

I keep getting this error.

Msg 117, Level 15, State 1, Line 16
The object name 'SERVERNAME.DATABASENAME' contains more than the maximum number of prefixes. The maximum is 2.

I'm using a linked servers and SQL server 2016. any suggestions?
Avatar of Zberteoc
Zberteoc
Flag of Canada image

You missed to show the essential:

How did you call that stored procedure?
Avatar of Tyler

ASKER

USE [TyLer]
GO


DECLARE      @return_value int

EXEC      @return_value = [dbo].[spCloneTable]
            @SourceSchema = N'DBO',
            @SourceTable = N'AE420',
            @DestinationSchema = N'tyler2].[DW].[DBO',
            @DestinationTable = N'AE420',
            @RecreateIfExists = 1

SELECT      'Return Value' = @return_value
This is your problem:

@DestinationSchema = N'tyler2].[DW].[DBO',

Try:

@DestinationSchema = N'DBO',
Avatar of Tyler

ASKER

understand but i want it to write to another server thus what i put in 'tyler2].[DW].[DBO' will include the linked server name in the schema coulmn creating '[Tyler2].[DW].[DBO].[ae420]  reference in the dynamic script when executed.  you need to read the script and understand what the error is saying.

The error
The object name 'SERVERNAME.DATABASENAME' contains more than the maximum number of prefixes. The maximum is 2.

here is what the sp generated
SELECT TOP (0) * INTO [tyler2].[dw].[DBO].[AE420] FROM [DBO].[AE420] and it returns the above error
Do you have defined the linked server with the name tyler2? If yes how do you refer to a table from a database on that server? I mean a simple select that works.
Avatar of Tyler

ASKER

OK so what i found is to create a table on a remote server you need to write the query like this:

exec ('USE adventureworks; 
 CREATE TABLE [dbo].[ae420]
(
      [Id] [INT] NOT NULL IDENTITY(1,1)
    , [grbreknr] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    , [factnr] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    , [cdherkomst] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    , [cdorder] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    , [prijs] [DECIMAL](10,2) NULL
    , [regelbedr] [DECIMAL](10,2) NULL
    , [cdprodukt] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    , [aantgelev] [INT] NULL  
    , CONSTRAINT [PK__ae420__3214EC071690F272] PRIMARY KEY CLUSTERED ([Id])
)') AT [LINKEDSERVERNAME]

Open in new window

This snip it works. dynamically it would be like this

DECLARE @object_name SYSNAME  
DECLARE @object_id INT  
DECLARE @SourceServerName SYSNAME = '[TYLER]'
DECLARE @SourceDBName SYSNAME = '[SOURCEDB]'
DECLARE @SourceSchemaName SYSNAME = 'dbo'
DECLARE @SourceTableName SYSNAME = 'ae420'

DECLARE @TargetServerName SYSNAME = '[TYLER2]'
DECLARE @TargetDBName SYSNAME = '[AdventureWorks]'
DECLARE @TargetSchemaName SYSNAME = 'dbo'
DECLARE @TargetTableName SYSNAME = 'ab149a'

DECLARE @SQL NVARCHAR(MAX)  
  
SELECT  
      @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'  
    , @object_id = [object_id]  
FROM (SELECT [object_id] = OBJECT_ID( @SourceTableName, 'U')) o  

PRINT @object_name
PRINT @object_id 
  
SELECT @SQL = 'exec (''USE ' + @TargetDBName + '; ' + CHAR(13)
SELECT @SQL = @SQL +' CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((  
SELECT CHAR(13) + '    , [' + c.name + '] ' +   
  CASE WHEN c.is_computed = 1  
       THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)  
       ELSE   
         CASE WHEN c.system_type_id != c.user_type_id   
              THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'   
              ELSE '[' + UPPER(tp.name) + ']'   
         END  +   
         CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')  
              THEN '(' + CASE WHEN c.max_length = -1   
                              THEN 'MAX'   
                              ELSE CAST(c.max_length AS VARCHAR(5))   
                         END + ')'  
              WHEN tp.name IN ('nvarchar', 'nchar')  
              THEN '(' + CASE WHEN c.max_length = -1   
                              THEN 'MAX'   
                              ELSE CAST(c.max_length / 2 AS VARCHAR(5))   
                         END + ')'  
              WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')   
              THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'  
              WHEN tp.name = 'decimal'  
              THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'  
              ELSE ''  
         END +  
         CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id   
              THEN ' COLLATE ' + c.collation_name  
              ELSE ''  
         END +  
         CASE WHEN c.is_nullable = 1   
              THEN ' NULL'  
              ELSE ' NOT NULL'  
         END +  
         CASE WHEN c.default_object_id != 0   
              THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +   
                         ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)  
              ELSE ''  
         END +   
         CASE WHEN cc.[object_id] IS NOT NULL   
              THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]  
              ELSE ''  
         END +  
         CASE WHEN c.is_identity = 1   
              THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +   
                           CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'   
              ELSE ''   
         END   
    END  
  FROM sys.columns c WITH(NOLOCK)  
  JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id  
  LEFT JOIN sys.check_constraints cc WITH(NOLOCK)   
         ON c.[object_id] = cc.parent_object_id   
        AND cc.parent_column_id = c.column_id  
  WHERE c.[object_id] = @object_id  
  ORDER BY c.column_id  
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') +   
    ISNULL((SELECT '  
    , CONSTRAINT [' + i.name + '] PRIMARY KEY ' +   
    CASE WHEN i.index_id = 1   
        THEN 'CLUSTERED'   
        ELSE 'NONCLUSTERED'   
    END +' (' + (  
    SELECT STUFF(CAST((  
        SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +  
                CASE WHEN ic.is_descending_key = 1  
                    THEN ' DESC'  
                    ELSE ''  
                END  
        FROM sys.index_columns ic WITH(NOLOCK)  
        WHERE i.[object_id] = ic.[object_id]  
            AND i.index_id = ic.index_id  
        FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'  
    FROM sys.indexes i WITH(NOLOCK)  
    WHERE i.[object_id] = @object_id  AND i.is_primary_key = 1), '') + CHAR(13) + ')'') AT '+ @TargetServerName +''

PRINT @SQL		
EXEC (@SQL)

-- this works from executing on source server to creating table on target server.

Open in new window


If anyone has a better solution please share.
If you have a linked server defined than you can refer to a table like this:

select * from linkedserver.database.owner.table

but if you don't have that you can't use a full qualification name!

Why don't you use the SQL server wizard to migrate a table to a different server? It is a simple and efficient method:

https://technet.microsoft.com/en-ca/library/ms178078(v=sql.105).aspx
Avatar of Tyler

ASKER

I am trying to write an automated way of creating and moving data one table at a time on demand rather then having to go through a bunch of steps manually from one server to another.  The EXECUTE AT clause executes the build on the remote server using the meta data for the table on the source server/place of original execution. we do not have the luxury of restore database as the data is live.

simplicity and execution with in a stored procedure is where I would like to be for this process, I also find it challenging both to my self and hopefully to the rest of the community on EE.

the syntax for a create table doesn't allow the server database name part to create the table on the remote server neither does the select.. into.. from  statement that would create the table. you would get the above mentioned error.
In order to do that you will have to generate the code for the table, complete structure and data, on one server and then execute it on the second server. What this procedure does with:

SELECT TOP (0) * INTO [tyler2].[dw].[DBO].[AE420] FROM [DBO].[AE420]

it would only work if you have defined a linked server.
Avatar of Tyler

ASKER

That is correct, I have defined linked servers. this is the current script i have working I just need to create the index if there are any.

DECLARE @object_name SYSNAME  
DECLARE @object_id INT  

DECLARE @SourceSchemaName SYSNAME = 'dbo'
DECLARE @SourceTableName SYSNAME = 'ae420'

DECLARE @TargetServerName SYSNAME = '[tyler2]'
DECLARE @TargetDBName SYSNAME = '[AdventureWorks]'
DECLARE @TargetSchemaName SYSNAME = 'dbo'
DECLARE @TargetTableName SYSNAME = 'ab149a'
DECLARE @TargetFilegroup SYSNAME 
DECLARE @SQL NVARCHAR(MAX)  
SET @SQL ='EXEC (''USE ' + @TargetDBName + '; drop table if exists [' + @TargetSchemaName + '].[' + @TargetTableName + ']'' ) AT '+ @TargetServerName +''

PRINT @SQL
EXEC (@SQL)
  
SELECT  
      @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'  
      --@object_name = '[' + @TargetSchemaName + '].[' + @TargetTableName + ']'  
    , @object_id = [object_id]  
FROM (SELECT [object_id] = OBJECT_ID( @SourceTableName, 'U')) o  

-- Get Filegroup for Table
SELECT TOP 1 
  @TargetFilegroup = d.name
FROM sys.filegroups d
 JOIN sys.indexes i ON i.data_space_id = d.data_space_id
 JOIN sys.tables t  ON t.object_id = i.object_id
WHERE 
 t.name= @SourceTableName  AND 
 t.schema_id = schema_id(@SourceSchemaName)

SELECT @SQL = 'exec (''USE ' + @TargetDBName + '; ' + CHAR(13)
-- SELECT @SQL = @SQL +' CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((  
SELECT @SQL = @SQL +' CREATE TABLE [' + @TargetSchemaName + '].[' + @TargetTableName + ']' + CHAR(13) + '(' + CHAR(13) + STUFF((  
SELECT CHAR(13) + '    , [' + c.name + '] ' +   
  CASE WHEN c.is_computed = 1  
       THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)  
       ELSE   
         CASE WHEN c.system_type_id != c.user_type_id   
              THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'   
              ELSE '[' + UPPER(tp.name) + ']'   
         END  +   
         CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')  
              THEN '(' + CASE WHEN c.max_length = -1   
                              THEN 'MAX'   
                              ELSE CAST(c.max_length AS VARCHAR(5))   
                         END + ')'  
              WHEN tp.name IN ('nvarchar', 'nchar')  
              THEN '(' + CASE WHEN c.max_length = -1   
                              THEN 'MAX'   
                              ELSE CAST(c.max_length / 2 AS VARCHAR(5))   
                         END + ')'  
              WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')   
              THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'  
              WHEN tp.name = 'decimal'  
              THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'  
              ELSE ''  
         END +  
         CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id   
              THEN ' COLLATE ' + c.collation_name  
              ELSE ''  
         END +  
         CASE WHEN c.is_nullable = 1   
              THEN ' NULL'  
              ELSE ' NOT NULL'  
         END +  
         CASE WHEN c.default_object_id != 0   
              THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +   
                         ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)  
              ELSE ''  
         END +   
         CASE WHEN cc.[object_id] IS NOT NULL   
              THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]  
              ELSE ''  
         END +  
         CASE WHEN c.is_identity = 1   
              THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +   
                           CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'   
              ELSE ''   
         END   
    END  
  FROM sys.columns c WITH(NOLOCK)  
  JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id  
  LEFT JOIN sys.check_constraints cc WITH(NOLOCK)   
         ON c.[object_id] = cc.parent_object_id   
        AND cc.parent_column_id = c.column_id  
  WHERE c.[object_id] = @object_id  
  ORDER BY c.column_id  
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') +   
    ISNULL((SELECT '  
    , CONSTRAINT [' + REPLACE(i.name, @SourceTableName, @TargetTableName)  + '] PRIMARY KEY ' +   
    CASE WHEN i.index_id = 1   
        THEN 'CLUSTERED'   
        ELSE 'NONCLUSTERED'   
    END +' (' + (  
    SELECT STUFF(CAST((  
        SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +  
                CASE WHEN ic.is_descending_key = 1  
                    THEN ' DESC'  
                    ELSE ''  
                END  
        FROM sys.index_columns ic WITH(NOLOCK)  
        WHERE i.[object_id] = ic.[object_id]  
            AND i.index_id = ic.index_id  
        FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'  
    FROM sys.indexes i WITH(NOLOCK)  
    WHERE i.[object_id] = @object_id  AND i.is_primary_key = 1), '') + CHAR(13) + ') on [' + @TargetFilegroup+'] '') AT '+ @TargetServerName +''

PRINT @SQL		

EXEC (@SQL)

/*
--populate the table something like this.

 BEGIN
   
	declare @s nvarchar(max)
	set @s= 
	'SET IDENTITY_INSERT ' + @TargetTableName + ' ON 
	INSERT ' + @TargetTableName + ' ( ' +
	(SELECT Stuff(( SELECT ', '+FieldName FROM @ShowFields FOR XML PATH('') ), 1, 2, ''))	+ ')
	SELECT * from ' + @SourceTableName + '
	SET IDENTITY_INSERT ' + @TargetTableName + ' OFF '

	print  @s 

    exec sp_executesql @s
END
*/

Open in new window


Any suggestions for attacking the indexing question would be great.
This script will generate the CREATE statements for all the indexes of all the tables in a database unless you specify a table name:

DECLARE
	@table_name varchar='AE420',
	@SQL varchar(8000)=''

SELECT  I.ID,
  REPLICATE(' ',4000) AS COLNAMES ,
  OBJECT_NAME(I.ID) AS TABLENAME,
  I.ID AS TABLEID,
  I.INDID AS INDEXID,
  I.NAME AS INDEXNAME,
  I.STATUS,
  INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,
  INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,
  INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR
  INTO #TMP
  FROM SYSINDEXES I
  WHERE I.INDID > 0 
  AND I.INDID < 255 
  AND (I.STATUS & 64)=0
  and 
  (
	OBJECT_NAME(I.ID)=@table_name
	OR 
	isnull(ltrim(rtrim(@table_name)),'')=''
  )
--uncomment below to eliminate PK or UNIQUE indexes;
--what i call 'normal' indexes
  --AND   INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE')       =0
  --AND   INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0
  --select * from #TMP

DECLARE
  @ISQL VARCHAR(4000),
  @TABLEID INT,
  @INDEXID INT,
  @MAXTABLELENGTH INT,
  @MAXINDEXLENGTH INT
  --USED FOR FORMATTING ONLY
    SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
    SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP

    DECLARE C1 CURSOR FOR
      SELECT TABLEID,INDEXID FROM #TMP  
    OPEN C1
      FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
        WHILE @@FETCH_STATUS <> -1
          BEGIN
	SET @ISQL = ''
	SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ',' FROM SYSINDEXES I
	INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
	INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
	WHERE I.INDID > 0 
	AND I.INDID < 255 
	AND (I.STATUS & 64)=0
	AND I.ID=@TABLEID AND I.INDID=@INDEXID
	ORDER BY SYSCOLUMNS.COLID
	UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID

	FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
         END
      CLOSE C1
      DEALLOCATE C1
  --AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA
  UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)

  SELECT  @SQL=@SQL+
  'CREATE ' 
    + CASE WHEN ISUNIQUE     = 1 THEN ' UNIQUE ' ELSE '        ' END 
    + CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE '           ' END 
    + ' INDEX [' + UPPER(INDEXNAME) + ']' 
    + SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))
    +' ON [' + UPPER(TABLENAME) + '] '
    + SPACE(@MAXTABLELENGTH - LEN(TABLENAME)) 
    + '(' + UPPER(COLNAMES) + ')' 
    + CASE WHEN INDEXFILLFACTOR = 0 THEN ''  ELSE  ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR)   END+'
'
    FROM #TMP

   --SELECT * FROM #TMP
   DROP TABLE #TMP

   SELECT @SQL	/* -- comment this line in order to actually execute the code
   EXEC (SQL)	--*/

Open in new window


I edited this comment to store the CREATE statements code into a variable which you can execute in the end. Make sure you check the code.
Avatar of Tyler

ASKER

Here is the table code to create the table on the testdb

USE [testdb]
GO

/****** Object:  Table [dbo].[ae420]    Script Date: 3/7/2017 1:29:28 PM ******/
DROP TABLE [dbo].[ae420]
GO

/****** Object:  Table [dbo].[ae420]    Script Date: 3/7/2017 1:29:28 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ae420](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[grbreknr] [VARCHAR](25) NULL,
	[factnr] [VARCHAR](25) NULL,
	[cdherkomst] [VARCHAR](25) NULL,
	[cdorder] [VARCHAR](25) NULL,
	[prijs] [DECIMAL](10, 2) NULL,
	[regelbedr] [DECIMAL](10, 2) NULL,
	[cdprodukt] [VARCHAR](25) NULL,
	[aantgelev] [INT] NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170228-145454] ON [dbo].[ae420]
(
	[grbreknr] ASC,
	[cdherkomst] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO

Open in new window


Here is the test data for this table

exec ('SET IDENTITY_INSERT [AdventureWorks].dbo.ab149a  ON; 
INSERT INTO [VIC-DATA-DLZ].[AdventureWorks].[dbo].[ab149a] 
	(Id, grbreknr, factnr, cdherkomst, cdorder, prijs, regelbedr, cdprodukt, aantgelev) Values
	(1,''bxdef'', ''abxdef'', '''', ''1'',  1, 10.1, '''', 1),
	(2,''axdef'', ''abxdef'', '''', ''31'',  1, 10.2, '''', 1),
	(3,''abdef'', ''abxdef'', '''', ''5'',  1, 10.3, '''', 1),
	(4,''abxef'', ''abxdef'', '''', ''3'', 1, 10.4, '''', 1); 
SET IDENTITY_INSERT [AdventureWorks].dbo.ab149a  OFF;') AT [VIC-DATA-DLZ]

Open in new window


Can you run on this structure and data, and see what your script yields I get this is all

(0 row(s) affected)

(0 row(s) affected)
In my code the declare lines have to be like this:

DECLARE
      @table_name varchar(255)='ae420',
      @SQL varchar(8000)=''

I forgot to specify the size for the @table_name so it would only take the first letter from the string.
Avatar of Tyler

ASKER

ok, I see it looks good I need to figure out the 4 rows

CREATE  UNIQUE  CLUSTERED  INDEX [PK__AE420__3214EC071690F272]       ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE                     INDEX [NONCLUSTEREDINDEX-20170228-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
CREATE  UNIQUE  CLUSTERED  INDEX [PK_AE420]                          ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE                     INDEX [NONCLUSTEREDINDEX-20170228-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95

as it has only a PK and a nonclustered index. any suggestion? (explanation)
From your data sample I only had 2 rows. Are you running it against another table?


CREATE  UNIQUE  CLUSTERED  INDEX [PK__AE420__3214EC07227E387D]       ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE                     INDEX [NONCLUSTEREDINDEX-20170228-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
Check your table see if you didn't have more indexes.
Avatar of Tyler

ASKER

The table create script above shows only the PK and the nonclustered index as generated from SSMS there is only one index

User generated image
Same here:
User generated image
It must be something in the code you are using. I only get 2 indexes with the code I posted above:
CREATE  UNIQUE  CLUSTERED  INDEX [PK__AE420__3214EC07227E387D]       ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE                     INDEX [NONCLUSTEREDINDEX-20170228-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95

Open in new window

Avatar of Tyler

ASKER

complete code used with your example:

	USE TYLER
	DROP TABLE IF EXISTS #tmp
	DECLARE
	@table_name varchar(255)='ae420',
        @SQL varchar(8000)=''


	SELECT  I.ID,
	REPLICATE(' ',4000) AS COLNAMES ,
	OBJECT_NAME(I.ID) AS TABLENAME,
	I.ID AS TABLEID,
	I.INDID AS INDEXID,
	I.NAME AS INDEXNAME,
	I.STATUS,
	INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,
	INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,
	INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR
	INTO #TMP
	FROM SYSINDEXES I
	WHERE I.INDID > 0 
	AND I.INDID < 255 
	AND (I.STATUS & 64)=0
	and 
	(
	OBJECT_NAME(I.ID)=@table_name
	OR 
	isnull(ltrim(rtrim(@table_name)),'')=''
	)
	
	DECLARE
	@ISQL VARCHAR(4000),
	@TABLEID INT,
	@INDEXID INT,
	@MAXTABLELENGTH INT,
	@MAXINDEXLENGTH INT
	--USED FOR FORMATTING ONLY
	SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
	SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP

	DECLARE C1 CURSOR FOR
		SELECT TABLEID,INDEXID FROM #TMP  
	OPEN C1
		FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
		WHILE @@FETCH_STATUS <> -1
			BEGIN
	SET @ISQL = ''
	SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ',' FROM SYSINDEXES I
	INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
	INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
	WHERE I.INDID > 0 
	AND I.INDID < 255 
	AND (I.STATUS & 64)=0
	AND I.ID=@TABLEID AND I.INDID=@INDEXID
	ORDER BY SYSCOLUMNS.COLID
	UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID
		
	FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
			END
		CLOSE C1
		DEALLOCATE C1
	--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA
	UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)

	SELECT  @SQL=@SQL+
	'CREATE ' 
	+ CASE WHEN ISUNIQUE     = 1 THEN ' UNIQUE ' ELSE '        ' END 
	+ CASE WHEN ISCLUSTERED  = 1 THEN ' CLUSTERED ' ELSE '           ' END 
	+ ' INDEX [' + UPPER(INDEXNAME) + ']' 
	+ SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))
	+' ON [' + UPPER(TABLENAME) + '] '
	+ SPACE(@MAXTABLELENGTH - LEN(TABLENAME)) 
	+ '(' + UPPER(COLNAMES) + ')' 
	+ CASE WHEN INDEXFILLFACTOR = 0 THEN ''  ELSE  ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR)   END+'
	'
	FROM #TMP

	--SELECT * FROM #TMP
	DROP TABLE #TMP

	--SELECT @SQL
	PRINT @SQL	/* -- comment this line in order to actually execute the code
	EXEC (SQL)	--*/

Open in new window

Avatar of Tyler

ASKER

results of previous posted code.

(4 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(4 row(s) affected)
CREATE  UNIQUE  CLUSTERED  INDEX [PK__AE420__3214EC071690F272]       ON [AE420] (ID) WITH FILLFACTOR = 95
      CREATE                     INDEX [NONCLUSTEREDINDEX-20170228-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
      CREATE  UNIQUE  CLUSTERED  INDEX [PK_AE420]                          ON [AE420] (ID) WITH FILLFACTOR = 95
      CREATE                     INDEX [NONCLUSTEREDINDEX-20170228-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
I just used the code you posted on the sable created with the sample code you gave me:


(2 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(2 row(s) affected)
CREATE  UNIQUE  CLUSTERED  INDEX [PK__AE420__3214EC07227E387D]       ON [AE420] (ID) WITH FILLFACTOR = 95
      CREATE                     INDEX [NONCLUSTEREDINDEX-20170228-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Post the results from that select here. I noticed that in your results message you have:

(4 row(s) affected)

while I have only 2! This is because in your case the script finds 4 rows for indexes, which you will see in that select.
Avatar of Tyler

ASKER

solution fits the effects I was going for.