Database

PHS241 wrote:

Is it possible to select this table's records and list them in column order animal, mineral, vegetable?

PHS241 wrote:

I have some tables that have an awful lot of columns

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

DECLARE curs CURSOR FOR 
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTable'
ORDER BY column_name

declare @qry as nvarchar(max)
set @qry = 'select '

DECLARE @col nvarchar(max)

OPEN curs
FETCH NEXT FROM curs into @col
WHILE @@FETCH_STATUS = 0
BEGIN
  set @qry += '['+@col + '],'
	FETCH NEXT FROM curs into @col
END

CLOSE curs
DEALLOCATE curs  

set @qry = substring(@qry,1,len(@qry)-1) + ' from yourTable'

exec(@qry)

set rowcount 1

declare @table as nvarchar(max)
declare @tbl_rowcount as int
declare @col_rowcount as int
declare @col nvarchar(max)
declare @qry as nvarchar(max)

select @table = table_name
from information_schema.tables
order by table_name 

select @tbl_rowcount = @@rowcount

while (@tbl_rowcount != 0)
begin

  set @qry = 'select ' + '''' + @table + '''' + ' as tableName, ' 
   
  select @col = column_name
  from information_schema.columns
  where table_name = @table
  order by column_name
  
  select @col_rowcount = @@rowcount
   
  while (@col_rowcount != 0)
  begin
      
      set @qry += '['+@col + '],'
      
      select @col = column_name
      from information_schema.columns
      where table_name = @table
      and column_name > @col
      order by column_name
      
      select @col_rowcount = @@rowcount
      
  end
     
  set @qry = substring(@qry,1,len(@qry)-1) + ' from ' + @table
  set rowcount 0
  exec(@qry)
  set rowcount 1

  select @table = table_name
  from information_schema.tables
  where table_name > @table
  order by table_name 
  
  set @tbl_rowcount = @@rowcount

end

set rowcount 0

Use [YourDatabase]
GO

SELECT SC.NAME FROM SYS.objects SO, SYS.columns SC
WHERE SO.TYPE = 'U' AND SO.name LIKE '[yourTableName]'
	AND SO.object_id = SC.object_id
ORDER BY SC.name

USE {enter your desired database name here};

DECLARE     @NameOfTable     VarChar(128);
SET     @NameOfTable = '{enter your desired table name here}';

DECLARE @SQLStatement VarChar(8000);

SET @SQLStatement =  'SELECT ''' + @NameOfTable + ''' AS TableName';

PRINT @SQLStatement;

EXEC(@SQLStatement);

WITH TableColumns_CTE
AS
(
    SELECT   T.name     AS  TableName
            ,C.name     AS  ColumnName
            ,ROW_NUMBER() OVER (PARTITION BY T.name ORDER BY T.name, C.name) Seq
    FROM    sys.tables T
    INNER JOIN sys.columns C
       ON   T.object_id = C.object_id
      AND   T.name = @NameOfTable
)

SELECT   @SQLStatement = @SQLStatement + ', ' + ColumnName
FROM    TableColumns_CTE;

SELECT   @SQLStatement = @SQLStatement + ' FROM ' + @NameOfTable + ';';

PRINT @SQLStatement;

EXEC(@SQLStatement);

zead wrote:

Is it possible to secure sql database using user name password, not the mixed mode user name password, (user name password ) on database it self to prevent it when restore on other sql engine(instance).

zead wrote:

the second questions is how to hide columns on sql server database engines, when open the database can't see the tables and columns until i enter the database user name password

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]